Thread: More Problems with ODBC and Access
Thanks to follks who posted ideas about resolving connection problems
between Access and pgsql. SOme ideas may have helped; some now.
I tried a couple different versions of the newer drivers. i updated the
MDAC install. I set registry timeout to 0.
On newer version of the ODBC drivers, including 7.02.0005 and 8.00.0004,
things got worse, not better. WIth them, the Access forms did not show any
sub-forms with parent child data. I could open all the tables individually,
and I can view the sub-form on its own, but if I open the main form, I can
an error and no child data. I have listed a snipped from the 8.00.0004
version log below.
So, I put the version back to 7.03.2000 which is where I started with last
summer. This version works better, except not stable (loses connection
after a while, as I described before)
Related: What makes big int id's in pg show up as decimals in Access? Could
this be part of the problem? Does it matter whether the id's are big ints
or just ints? Do any of the settings refer to this? The documentation of
the settings leaves some room for improvement...
I also have seen some random comments about the present of time stamp
fields, booleans, memo/text fields. I'm not sure its worth restructuring
the database and the web site around it in order to test these
possibilities without more insight.
Also, does anyone have a sense of whether links created in code are
deficient in some ways compared to links created through Access IDE?
here's sample problematic sql. In opening the form, first the log shows
query for main table (individual...), which works. Then shows trying to get
a child table, org affiliations for an individual, which failed on recent
versions of the driver.
conn=201676480, query='SELECT
"public"."org_ind"."org_ind_id","public"."individual"."firstname"
,"public"."individual"."lastname" FROM
"public"."org_ind","public"."individual" WHERE (( 4129'
ERROR from backend during send_query: 'ERROR: syntax error at end of input
at character 168'
STATEMENT ERROR: func=SC_execute, desc='', errnum=7, errmsg='Error while
executing the query'
------------------------------------------------------------
hdbc=201676480, stmt=201912520, result=174373392
manual_result=0, prepare=0, internal=0
bindings=0, bindings_allocated=0
parameters=174370736, parameters_allocated=1
statement_type=0, statement='SELECT
"public"."org_ind"."org_ind_id","public"."individual"."firstname"
,"public"."individual"."lastname" FROM
"public"."org_ind","public"."individual" WHERE (( ? =
"public"."org_ind"."org_id" ) AND ("public"."org_ind"."ind_id" =
"public"."individual"."individual_id" ) ) '
stmt_with_params='SELECT
"public"."org_ind"."org_ind_id","public"."individual"."firstname"
,"public"."individual"."lastname" FROM
"public"."org_ind","public"."individual" WHERE (( 4129'
data_at_exec=-1, current_exec_param=-1, put_data=0
currTuple=-1, current_col=-1, lobj_fd=-1
maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0,
scroll_concurrency=1
cursor_name='SQL_CUR0C08F0C8'
----------------QResult Info -------------------------------
fields=174381160, manual_tuples=0, backend_tuples=0,
tupleField=0, conn=0
fetch_count=0, num_total_rows=0, num_fields=0,
cursor='(NULL)'
message='ERROR: syntax error at end of input at
character 168', command='(NULL)', notice='(NULL)'
status=7, inTuples=0
CONN ERROR: func=SC_execute, desc='', errnum=110, errmsg='ERROR: syntax
error at end of input at character 168'
------------------------------------------------------------
henv=174339688, conn=201676480, status=1, num_stmts=16
sock=201675736, stmts=174383240, lobj_type=-999
---------------- Socket Info -------------------------------
socket=836, reverse=0, errornumber=0, errormsg='(NULL)'
buffer_in=174385016, buffer_out=201655216
buffer_filled_in=1, buffer_filled_out=0, buffer_read_in=1
sorry for long post.
Steven A Backman - Database Designs Associates, Inc.
PO Box 715 - Jamaica Plain, MA 02130
617-889-0929 personal fax: 309-404-2252
sbackman@dbdes.com www.dbdes.com
Steve Backman wrote: > Related: What makes big int id's in pg show up as decimals in Access? > Could this be part of the problem? Does it matter whether the id's are > big ints or just ints? Do any of the settings refer to this? The > documentation of the settings leaves some room for improvement... I'm not sure if Access supports 64-bit integers. If not, it could be that they're being mapped to floating-point (doubles presumably). That probably will cause difficulties if you're using bigints for ID numbers. -- Richard Huxton Archonet Ltd
On the BIG-INT IDs. I'm pretty sure Access has an Integer of 16-bit and
a Long Integer of 32-bit that it understands. In PG that's int2 and int4
respectively. I think int8 or 64-bit are problematic for Access because
the ODBC conversion must choose between string and decimal and float
single or double. I am really not sure if decimal or float(singles or
doubles) serve well as a primary keys, especially for an exact match
parent-child / primary key-foreign key relationship.
It seem a little limiting but I try to limit my non-string primary keys
to int4 and serial int4 (not big serial). If it works Access should not
complain about being unable to identify a primary key if you link a
table manually.I would check my ODBC driver settings on how it treats
int8 (datasource button - tab 2). Unfortunately, I haven't personally
done forms with subforms for parent-child relations using the PostgreSQL
ODBC served datasource. I've only done it with Access tables, but I do
wish you good luck.
Steve Backman wrote:
> Thanks to follks who posted ideas about resolving connection problems
> between Access and pgsql. SOme ideas may have helped; some now.
>
> I tried a couple different versions of the newer drivers. i updated
> the MDAC install. I set registry timeout to 0.
>
> On newer version of the ODBC drivers, including 7.02.0005 and
> 8.00.0004, things got worse, not better. WIth them, the Access forms
> did not show any sub-forms with parent child data. I could open all
> the tables individually, and I can view the sub-form on its own, but
> if I open the main form, I can an error and no child data. I have
> listed a snipped from the 8.00.0004 version log below.
>
> So, I put the version back to 7.03.2000 which is where I started with
> last summer. This version works better, except not stable (loses
> connection after a while, as I described before)
>
> Related: What makes big int id's in pg show up as decimals in Access?
> Could this be part of the problem? Does it matter whether the id's are
> big ints or just ints? Do any of the settings refer to this? The
> documentation of the settings leaves some room for improvement...
>
> I also have seen some random comments about the present of time stamp
> fields, booleans, memo/text fields. I'm not sure its worth
> restructuring the database and the web site around it in order to test
> these possibilities without more insight.
>
> Also, does anyone have a sense of whether links created in code are
> deficient in some ways compared to links created through Access IDE?
>
> here's sample problematic sql. In opening the form, first the log
> shows query for main table (individual...), which works. Then shows
> trying to get a child table, org affiliations for an individual, which
> failed on recent versions of the driver.
>
> conn=201676480, query='SELECT
> "public"."org_ind"."org_ind_id","public"."individual"."firstname"
> ,"public"."individual"."lastname" FROM
> "public"."org_ind","public"."individual" WHERE (( 4129'
> ERROR from backend during send_query: 'ERROR: syntax error at end of
> input at character 168'
> STATEMENT ERROR: func=SC_execute, desc='', errnum=7, errmsg='Error
> while executing the query'
>
> ------------------------------------------------------------
> hdbc=201676480, stmt=201912520, result=174373392
> manual_result=0, prepare=0, internal=0
> bindings=0, bindings_allocated=0
> parameters=174370736, parameters_allocated=1
> statement_type=0, statement='SELECT
> "public"."org_ind"."org_ind_id","public"."individual"."firstname"
> ,"public"."individual"."lastname" FROM
> "public"."org_ind","public"."individual" WHERE (( ? =
> "public"."org_ind"."org_id" ) AND ("public"."org_ind"."ind_id" =
> "public"."individual"."individual_id" ) ) '
> stmt_with_params='SELECT
> "public"."org_ind"."org_ind_id","public"."individual"."firstname"
> ,"public"."individual"."lastname" FROM
> "public"."org_ind","public"."individual" WHERE (( 4129'
> data_at_exec=-1, current_exec_param=-1, put_data=0
> currTuple=-1, current_col=-1, lobj_fd=-1
> maxRows=0, rowset_size=1, keyset_size=0,
> cursor_type=0, scroll_concurrency=1
> cursor_name='SQL_CUR0C08F0C8'
> ----------------QResult Info
> -------------------------------
> fields=174381160, manual_tuples=0, backend_tuples=0,
> tupleField=0, conn=0
> fetch_count=0, num_total_rows=0, num_fields=0,
> cursor='(NULL)'
> message='ERROR: syntax error at end of input at
> character 168', command='(NULL)', notice='(NULL)'
> status=7, inTuples=0
> CONN ERROR: func=SC_execute, desc='', errnum=110, errmsg='ERROR:
> syntax error at end of input at character 168'
> ------------------------------------------------------------
> henv=174339688, conn=201676480, status=1, num_stmts=16
> sock=201675736, stmts=174383240, lobj_type=-999
> ---------------- Socket Info -------------------------------
> socket=836, reverse=0, errornumber=0, errormsg='(NULL)'
> buffer_in=174385016, buffer_out=201655216
> buffer_filled_in=1, buffer_filled_out=0, buffer_read_in=1
>
> sorry for long post.
>
>
> Steven A Backman - Database Designs Associates, Inc.
> PO Box 715 - Jamaica Plain, MA 02130
> 617-889-0929 personal fax: 309-404-2252
> sbackman@dbdes.com www.dbdes.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org