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(at)dbdes(dot)com www.dbdes.com