Skip site navigation (1) Skip section navigation (2)

Re: More Problems with ODBC and Access

From: "Greg Campbell" <greg(dot)campbell(at)us(dot)michelin(dot)com>
To: Steve Backman <earlysteve(at)earthlink(dot)net>
Cc: pgsql-odbc(at)postgresql(dot)org
Subject: Re: More Problems with ODBC and Access
Date: 2005-02-18 17:22:51
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-odbc
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
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?

Attachment: greg.campbell.vcf
Description: text/x-vcard (283 bytes)

In response to

pgsql-odbc by date

Next:From: Eric EDate: 2005-02-18 17:51:04
Subject: Re: Cannot link to postgres 8.0.0 databases using ODBC from
Previous:From: laurie.burrowDate: 2005-02-18 16:53:11
Subject: Re: Cannot link to postgres 8.0.0 databases using ODBC from

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group