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