Thread: ODBC & Access [Try 2]
I'm trying to use Access as a front end for Postgres. This is mainly for data-entry and manual updates. I was able to establish an ODBC connection to the server without any problems. I was also able to link the tables that I need. I linked 8 tables total. 7 of them work just fine and I can see all the data in them. The last one though, only displays '#DELETED' for each column of each row. I checked the permissions on all the tables and they're the same -- I have read access (all I need for now, right?) Any idea what's going on? This is PostgreSQL 7.3.2 on FreeBSD with the PostgreSQL ODBC driver version 7.02.00.05 on Win XP. -------------------------- David Olbersen
--- David Olbersen <DOlbersen@stbernard.com> wrote: > I'm trying to use Access as a front end for > Postgres. This is > mainly for data-entry and manual updates. > > I was able to establish an ODBC connection to the > server > without any problems. I was also able to link the > tables that > I need. I linked 8 tables total. > > 7 of them work just fine and I can see all the data > in them. > The last one though, only displays '#DELETED' for > each column > of each row. I checked the permissions on all the > tables and > they're the same -- I have read access (all I need > for now, right?) > > Any idea what's going on? This is PostgreSQL 7.3.2 > on FreeBSD with the PostgreSQL ODBC driver version > 7.02.00.05 on Win XP. <guess> Make sure driver option "row versioning" is checked. Also, make sure that the offending table has a primary key that Access is recognizing as such. </guess> If both of those conditions are met, and you still have a problem, please post your driver settings. __________________________________________________ Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo http://search.yahoo.com
> -----Original Message-----
> From: Jeff Eckermann [mailto:jeff_eckermann@yahoo.com]
> Sent: Thursday, April 24, 2003 12:08 PM
> To: David Olbersen; pgsql-novice@postgresql.org
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] ODBC & Access [Try 2]
>
> <guess>
> Make sure driver option "row versioning" is checked.
> Also, make sure that the offending table has a primary
> key that Access is recognizing as such.
> </guess>
> If both of those conditions are met, and you still
> have a problem, please post your driver settings.
Turned on row versioning, no change.
When I link the table, Access doesn't ask me to chose the primary key. When I look at the Design View for that table
it'schosen a primary key. The field it's chosen doesn't have any NULL values and all the rows for that field are
unique,so it should be OK, right? The column that it chose is NOT a numeric field, but text. Would that cause a
problem?
Driver settings:
================
This is a System DSN.
Datasource options (page 1)
Disable Genetic Optimizer = Checked
KSQO = Checked
Recognize Unique Indexes = Checked
Use Declare/Fetch = Checked
CommLog = Checked
Parse Statements = UNchecked
Cancel as FreeStmt = UNchecked
MyLog = Checked
Unknown Sizes = Maximum
Data Type Options:
Text as LongVarChar = Checked
Unknowns as LongVarChar = UNchecked
Bools as Char = Checked
Miscellanous:
Max Varchar = 254
Max LongVarChar = 8190
Cache Size = 100
SysTable Prefixes = dd_;
Datasource options (page 2)
Read Only = UNchecked
Show System Tables = UNchecked
LF <-> CR/LF conversion = Checked
Updatable Cursors = Checked
Row Versioning = Checked (now, wasn't before)
Disallow Premature = UNchecked
True is -1 = UNchecked
Int8 as = default
Protocol = 7.x,6.4+
OID Options:
Show Column = UNchecked
Connect Settings = <blank>
--------------------------
David Olbersen
iGuard Engineer
11415 West Bernardo Court
San Diego, CA 92127
1-858-676-2277 x2152
maybe it's related to sorting in your environment, the client's environment, or the server's environment? Case sensitivevs. not? (PS, I can't wait till UTF-8 gets totally ubiquitous.) David Olbersen wrote: >>-----Original Message----- >>From: Jeff Eckermann [mailto:jeff_eckermann@yahoo.com] >>Sent: Thursday, April 24, 2003 12:08 PM >>To: David Olbersen; pgsql-novice@postgresql.org >>Cc: pgsql-general@postgresql.org >>Subject: Re: [GENERAL] ODBC & Access [Try 2] >> >><guess> >>Make sure driver option "row versioning" is checked. >>Also, make sure that the offending table has a primary >>key that Access is recognizing as such. >></guess> >>If both of those conditions are met, and you still >>have a problem, please post your driver settings. > > > Turned on row versioning, no change. > > When I link the table, Access doesn't ask me to chose the primary key. When I look at the Design View for that table it'schosen a primary key. The field it's chosen doesn't have any NULL values and all the rows for that field are unique,so it should be OK, right? The column that it chose is NOT a numeric field, but text. Would that cause a problem? > > Driver settings: > ================ > This is a System DSN. > > Datasource options (page 1) > Disable Genetic Optimizer = Checked > KSQO = Checked > Recognize Unique Indexes = Checked > Use Declare/Fetch = Checked > CommLog = Checked > Parse Statements = UNchecked > Cancel as FreeStmt = UNchecked > MyLog = Checked > Unknown Sizes = Maximum > Data Type Options: > Text as LongVarChar = Checked > Unknowns as LongVarChar = UNchecked > Bools as Char = Checked > Miscellanous: > Max Varchar = 254 > Max LongVarChar = 8190 > Cache Size = 100 > SysTable Prefixes = dd_; > > Datasource options (page 2) > Read Only = UNchecked > Show System Tables = UNchecked > LF <-> CR/LF conversion = Checked > Updatable Cursors = Checked > Row Versioning = Checked (now, wasn't before) > Disallow Premature = UNchecked > True is -1 = UNchecked > Int8 as = default > Protocol = 7.x,6.4+ > OID Options: > Show Column = UNchecked > Connect Settings = <blank> > > > -------------------------- > David Olbersen > iGuard Engineer > 11415 West Bernardo Court > San Diego, CA 92127 > 1-858-676-2277 x2152 > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
Maybe, but I REALLY doubt it. Like I said, these other tables with very similar structures and very similar data display just fine. It's just this one. -------------------------- David Olbersen iGuard Engineer 11415 West Bernardo Court San Diego, CA 92127 1-858-676-2277 x2152 > -----Original Message----- > From: Dennis Gearon [mailto:gearond@cvc.net] > Sent: Thursday, April 24, 2003 12:38 PM > To: David Olbersen > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] ODBC & Access [Try 2] > > > maybe it's related to sorting in your environment, the > client's environment, or the server's environment? Case > sensitive vs. not? (PS, I can't wait till UTF-8 gets totally > ubiquitous.) > > David Olbersen wrote: > >>-----Original Message----- > >>From: Jeff Eckermann [mailto:jeff_eckermann@yahoo.com] > >>Sent: Thursday, April 24, 2003 12:08 PM > >>To: David Olbersen; pgsql-novice@postgresql.org > >>Cc: pgsql-general@postgresql.org > >>Subject: Re: [GENERAL] ODBC & Access [Try 2] > >> > >><guess> > >>Make sure driver option "row versioning" is checked. > >>Also, make sure that the offending table has a primary > >>key that Access is recognizing as such. > >></guess> > >>If both of those conditions are met, and you still > >>have a problem, please post your driver settings. > > > > > > Turned on row versioning, no change. > > > > When I link the table, Access doesn't ask me to chose the > primary key. When I look at the Design View for that table > it's chosen a primary key. The field it's chosen doesn't have > any NULL values and all the rows for that field are unique, > so it should be OK, right? The column that it chose is NOT a > numeric field, but text. Would that cause a problem? > > > > Driver settings: > > ================ > > This is a System DSN. > > > > Datasource options (page 1) > > Disable Genetic Optimizer = Checked > > KSQO = Checked > > Recognize Unique Indexes = Checked > > Use Declare/Fetch = Checked > > CommLog = Checked > > Parse Statements = UNchecked > > Cancel as FreeStmt = UNchecked > > MyLog = Checked > > Unknown Sizes = Maximum > > Data Type Options: > > Text as LongVarChar = Checked > > Unknowns as LongVarChar = UNchecked > > Bools as Char = Checked > > Miscellanous: > > Max Varchar = 254 > > Max LongVarChar = 8190 > > Cache Size = 100 > > SysTable Prefixes = dd_; > > > > Datasource options (page 2) > > Read Only = UNchecked > > Show System Tables = UNchecked > > LF <-> CR/LF conversion = Checked > > Updatable Cursors = Checked > > Row Versioning = Checked (now, wasn't before) > > Disallow Premature = UNchecked > > True is -1 = UNchecked > > Int8 as = default > > Protocol = 7.x,6.4+ > > OID Options: > > Show Column = UNchecked > > Connect Settings = <blank> > > > > > > -------------------------- > > David Olbersen > > iGuard Engineer > > 11415 West Bernardo Court > > San Diego, CA 92127 > > 1-858-676-2277 x2152 > > > > > > ---------------------------(end of > broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org > > > >
You said it was the **ONE** table that had a text field for an index right? David Olbersen wrote: > Maybe, but I REALLY doubt it. > > Like I said, these other tables with very similar structures and very similar data display just fine. It's just this one. > > -------------------------- > David Olbersen > iGuard Engineer > 11415 West Bernardo Court > San Diego, CA 92127 > 1-858-676-2277 x2152 > > > >>-----Original Message----- >>From: Dennis Gearon [mailto:gearond@cvc.net] >>Sent: Thursday, April 24, 2003 12:38 PM >>To: David Olbersen >>Cc: pgsql-general@postgresql.org >>Subject: Re: [GENERAL] ODBC & Access [Try 2] >> >> >>maybe it's related to sorting in your environment, the >>client's environment, or the server's environment? Case >>sensitive vs. not? (PS, I can't wait till UTF-8 gets totally >>ubiquitous.) >> >>David Olbersen wrote: >> >>>>-----Original Message----- >>>>From: Jeff Eckermann [mailto:jeff_eckermann@yahoo.com] >>>>Sent: Thursday, April 24, 2003 12:08 PM >>>>To: David Olbersen; pgsql-novice@postgresql.org >>>>Cc: pgsql-general@postgresql.org >>>>Subject: Re: [GENERAL] ODBC & Access [Try 2] >>>> >>>><guess> >>>>Make sure driver option "row versioning" is checked. >>>>Also, make sure that the offending table has a primary >>>>key that Access is recognizing as such. >>>></guess> >>>>If both of those conditions are met, and you still >>>>have a problem, please post your driver settings. >>> >>> >>>Turned on row versioning, no change. >>> >>>When I link the table, Access doesn't ask me to chose the >> >>primary key. When I look at the Design View for that table >>it's chosen a primary key. The field it's chosen doesn't have >>any NULL values and all the rows for that field are unique, >>so it should be OK, right? The column that it chose is NOT a >>numeric field, but text. Would that cause a problem? >> >>>Driver settings: >>>================ >>>This is a System DSN. >>> >>>Datasource options (page 1) >>> Disable Genetic Optimizer = Checked >>> KSQO = Checked >>> Recognize Unique Indexes = Checked >>> Use Declare/Fetch = Checked >>> CommLog = Checked >>> Parse Statements = UNchecked >>> Cancel as FreeStmt = UNchecked >>> MyLog = Checked >>> Unknown Sizes = Maximum >>> Data Type Options: >>> Text as LongVarChar = Checked >>> Unknowns as LongVarChar = UNchecked >>> Bools as Char = Checked >>> Miscellanous: >>> Max Varchar = 254 >>> Max LongVarChar = 8190 >>> Cache Size = 100 >>> SysTable Prefixes = dd_; >>> >>>Datasource options (page 2) >>> Read Only = UNchecked >>> Show System Tables = UNchecked >>> LF <-> CR/LF conversion = Checked >>> Updatable Cursors = Checked >>> Row Versioning = Checked (now, wasn't before) >>> Disallow Premature = UNchecked >>> True is -1 = UNchecked >>> Int8 as = default >>> Protocol = 7.x,6.4+ >>> OID Options: >>> Show Column = UNchecked >>> Connect Settings = <blank> >>> >>> >>>-------------------------- >>>David Olbersen >>>iGuard Engineer >>>11415 West Bernardo Court >>>San Diego, CA 92127 >>>1-858-676-2277 x2152 >>> >>> >>>---------------------------(end of >> >>broadcast)--------------------------- >> >>>TIP 1: subscribe and unsubscribe commands go to >> >>majordomo@postgresql.org >> >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
David, > 7 of them work just fine and I can see all the data in them. > The last one though, only displays '#DELETED' for each column > of each row. I checked the permissions on all the tables and > they're the same -- I have read access (all I need for now, right?) > > Any idea what's going on? This is PostgreSQL 7.3.2 on FreeBSD with the PostgreSQL ODBC driver version 7.02.00.05 on Win XP. My guess is that the table definition has changed since you attached it from access. I'd suggest dropping the linked table and re-linking it. For further help, I suggest joining the pgsql-odbc mailing list and/or the pgodbc project's mailing list. -- -Josh Berkus Aglio Database Solutions San Francisco
Two recommendations. 1) Try the snapshot dll from http://www.geocities.jp/inocchichichi/psqlodbc/. 2) Uncheck the box for updatable cursors. On Thursday 24 April 2003 12:24 pm, David Olbersen wrote: > > -----Original Message----- > > From: Jeff Eckermann [mailto:jeff_eckermann@yahoo.com] > > Sent: Thursday, April 24, 2003 12:08 PM > > To: David Olbersen; pgsql-novice@postgresql.org > > Cc: pgsql-general@postgresql.org > > Subject: Re: [GENERAL] ODBC & Access [Try 2] > > > > <guess> > > Make sure driver option "row versioning" is checked. > > Also, make sure that the offending table has a primary > > key that Access is recognizing as such. > > </guess> > > If both of those conditions are met, and you still > > have a problem, please post your driver settings. > > Turned on row versioning, no change. > > When I link the table, Access doesn't ask me to chose the primary key. When > I look at the Design View for that table it's chosen a primary key. The > field it's chosen doesn't have any NULL values and all the rows for that > field are unique, so it should be OK, right? The column that it chose is > NOT a numeric field, but text. Would that cause a problem? > > Driver settings: > ================ > This is a System DSN. > > Datasource options (page 1) > Disable Genetic Optimizer = Checked > KSQO = Checked > Recognize Unique Indexes = Checked > Use Declare/Fetch = Checked > CommLog = Checked > Parse Statements = UNchecked > Cancel as FreeStmt = UNchecked > MyLog = Checked > Unknown Sizes = Maximum > Data Type Options: > Text as LongVarChar = Checked > Unknowns as LongVarChar = UNchecked > Bools as Char = Checked > Miscellanous: > Max Varchar = 254 > Max LongVarChar = 8190 > Cache Size = 100 > SysTable Prefixes = dd_; > > Datasource options (page 2) > Read Only = UNchecked > Show System Tables = UNchecked > LF <-> CR/LF conversion = Checked > Updatable Cursors = Checked > Row Versioning = Checked (now, wasn't before) > Disallow Premature = UNchecked > True is -1 = UNchecked > Int8 as = default > Protocol = 7.x,6.4+ > OID Options: > Show Column = UNchecked > Connect Settings = <blank> > > > -------------------------- > David Olbersen > iGuard Engineer > 11415 West Bernardo Court > San Diego, CA 92127 > 1-858-676-2277 x2152 > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Thanks for the suggestion, but that didn't do it either. I installed that dll in C:\Windows\System32, created a new DSN using that driver and the problem still exists. -------------------------- David Olbersen iGuard Engineer 11415 West Bernardo Court San Diego, CA 92127 1-858-676-2277 x2152 > -----Original Message----- > From: Adrian Klaver [mailto:aklaver@attbi.com] > Sent: Thursday, April 24, 2003 7:40 PM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] ODBC & Access [Try 2] > > > Two recommendations. > 1) Try the snapshot dll from > http://www.geocities.jp/inocchichichi/psqlodbc/. > 2) Uncheck the box for updatable cursors. > > On Thursday 24 April 2003 12:24 pm, David Olbersen wrote: > > > -----Original Message----- > > > From: Jeff Eckermann [mailto:jeff_eckermann@yahoo.com] > > > Sent: Thursday, April 24, 2003 12:08 PM > > > To: David Olbersen; pgsql-novice@postgresql.org > > > Cc: pgsql-general@postgresql.org > > > Subject: Re: [GENERAL] ODBC & Access [Try 2] > > > > > > <guess> > > > Make sure driver option "row versioning" is checked. > > > Also, make sure that the offending table has a primary > > > key that Access is recognizing as such. > > > </guess> > > > If both of those conditions are met, and you still > > > have a problem, please post your driver settings. > > > > Turned on row versioning, no change. > > > > When I link the table, Access doesn't ask me to chose the > primary key. When > > I look at the Design View for that table it's chosen a > primary key. The > > field it's chosen doesn't have any NULL values and all the > rows for that > > field are unique, so it should be OK, right? The column > that it chose is > > NOT a numeric field, but text. Would that cause a problem? > > > > Driver settings: > > ================ > > This is a System DSN. > > > > Datasource options (page 1) > > Disable Genetic Optimizer = Checked > > KSQO = Checked > > Recognize Unique Indexes = Checked > > Use Declare/Fetch = Checked > > CommLog = Checked > > Parse Statements = UNchecked > > Cancel as FreeStmt = UNchecked > > MyLog = Checked > > Unknown Sizes = Maximum > > Data Type Options: > > Text as LongVarChar = Checked > > Unknowns as LongVarChar = UNchecked > > Bools as Char = Checked > > Miscellanous: > > Max Varchar = 254 > > Max LongVarChar = 8190 > > Cache Size = 100 > > SysTable Prefixes = dd_; > > > > Datasource options (page 2) > > Read Only = UNchecked > > Show System Tables = UNchecked > > LF <-> CR/LF conversion = Checked > > Updatable Cursors = Checked > > Row Versioning = Checked (now, wasn't before) > > Disallow Premature = UNchecked > > True is -1 = UNchecked > > Int8 as = default > > Protocol = 7.x,6.4+ > > OID Options: > > Show Column = UNchecked > > Connect Settings = <blank> > > > > > > -------------------------- > > David Olbersen > > iGuard Engineer > > 11415 West Bernardo Court > > San Diego, CA 92127 > > 1-858-676-2277 x2152 > > > > > > ---------------------------(end of > broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org > > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >