Re: More Problems with ODBC and Access

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(at)dbdes(dot)com www.dbdes.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org