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'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
Import Notes
Resolved by subject fallback
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:
Show quoted text
-----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
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
Show quoted text
-----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
Import Notes
Resolved by subject fallback
You said it was the **ONE** table that had a text field for an index right?
David Olbersen wrote:
Show quoted text
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.
Show quoted text
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
Show quoted text
-----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
Import Notes
Resolved by subject fallback