Re: Access2000 & sequence as primary key in view : #DELETED

>
> I am quite sure that the problem comes from the sequence being used in
> a view.
>
That's not the problem, a sequence is just a integer generator for a
integer field/column that has a nextval function in the columns default
value.
The view and the table don't know anything about the sequence other than
the reference to it in the columns default value.
Sequences are completely independent of any view or table and a serial
type is not a real type since it simply places a nextval function call
in the columns default value(and some entries in the pg_depend table).

Here is a example table that at one time always got the # deleted in
Access 97, then we modified it like so:

CREATE TABLE 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 INDEX mf_accum_table_pg_idx2 ON ptr172.mf_accum_table_pg USING
btree (bank);
CREATE INDEX mf_accum_table_pg_idx0 ON ptr172.mf_accum_table_pg USING
btree (blockid);
CREATE UNIQUE INDEX mf_accum_table_pg_idx3 ON ptr172.mf_accum_table_pg
USING btree (bank, cusip, side, settledate, cash);

we simply added a record id field to every table and made that the PK,
then to enforce the constraints we had before in the PK we created a
unique index.

We have not had a problem with the # deleted entries showing up since we
did this to all the tables in Postgresql.

When using ODBC ACCESS does use the PK in the PG tables and it only
works with a single integer value. Maybe newer versions of Access
behave differently with ODBC linked tables but I kind of doubt it.

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration