Thread: Problem with the PGSQL-ODBC driver and MS Access
Hi, I have a user who has been converting some data from access to a Postgresql 8.1.3 backend and we ran into a issue where some records would show up in Access with all the fields as #deleted The ODBC driver is the version from December 2005. In one instance they appeared scattered throughout the result set, and the last time they were all at the end. This table had a PK with 5 columns as the PK: CREATE TABLE ptr172.mf_accum_table_pg ( bank varchar(2) NOT NULL, cusip varchar(11) NOT NULL, secdesc varchar(37), side varchar(9) NOT NULL, agent varchar(4), fdaccount varchar(17), trans varchar(4), settledate timestamp NOT NULL, blockid varchar(12), cash double precision NOT NULL, rec_id serial NOT NULL, CONSTRAINT mf_accum_table_pg_pk PRIMARY KEY (bank,cusip,side,blockid,cash) )WITHOUT OIDS; If I change the PK to use the rec_id column and add a UNIQUE index as shown below, the #deleted rows in the access linked table disappear. I am unsure if this is a Access 97 issue or a bug in the ODBC driver. CREATE TABLE ptr172.mf_accum_table_pg ( bank varchar(2) NOT NULL, cusip varchar(11) NOT NULL, secdesc varchar(37), side varchar(9) NOT NULL, agent varchar(4), fdaccount varchar(17), trans varchar(4), settledate timestamp NOT NULL, blockid varchar(12), cash double precision NOT NULL, rec_id serial NOT NULL, CONSTRAINT mf_accum_table_pg_pk PRIMARY KEY (rec_id) )WITHOUT OIDS; -- Indexes CREATE UNIQUE INDEX mf_accum_table_pg_idx3 ON ptr172.mf_accum_table_pg USING btree (bank, cusip, side, settledate, cash); -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration
> Hi, > I have a user who has been converting some data from access to a > Postgresql 8.1.3 backend and we ran into > a issue where some records would show up in Access with all the fields > as #deleted Have you searched the mail-list archive? #Deleted should be good start for searching. Regards, Luf
Ludek Finstrle wrote: >> Hi, >> I have a user who has been converting some data from access to a >> Postgresql 8.1.3 backend and we ran into >> a issue where some records would show up in Access with all the fields >> as #deleted >> > > Have you searched the mail-list archive? #Deleted should be good start > for searching. > > Regards, > > Luf > > yep, nothing that relates to this particular issue. -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration
Wed, Apr 26, 2006 at 01:32:31PM -0500, Tony Caduto napsal(a): > Ludek Finstrle wrote: > >>Hi, > >>I have a user who has been converting some data from access to a > >>Postgresql 8.1.3 backend and we ran into > >>a issue where some records would show up in Access with all the fields > >>as #deleted > >> > > > >Have you searched the mail-list archive? #Deleted should be good start > >for searching. > > yep, nothing that relates to this particular issue. Hmm I'm unable to locate it right now. But I remember that someone talk about it since November 2005. Maybe it is in Bug-list on pgfoundry.org. Someone answer similar question. Regards, Luf
At 18:44 26/04/2006, you wrote:
>Hi,
>I have a user who has been converting some data from access to a
>Postgresql 8.1.3 backend and we ran into
>a issue where some records would show up in Access with all the fields
>as #deleted
I've seen this sometimes when using "timestamp": access doesn't
understand well the fractionnal part of it. You should instead use
timestamp(0).
db=> create temp table zz ( field1 timestamp(0), field2 timestamp );
CREATE TABLE
db=> insert into zz values (now(),now());
INSERT 0 1
db=> select * from zz ;
field1 | field2
---------------------+----------------------------
2006-04-26 22:44:42 | 2006-04-26 22:44:41.893001
(1 row)
----------------------------------------------------------
field1 will be understandable by access, not field2.
msaccess in that case will show "deleted" in all cells.
hopes that help . P.