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

Arnaud Lesauvage wrote:
> Hi list !
>
> I created a view in postgresql (quite complex, multiple tables are
> linked). The aim is to link it in acces via ODBC.
>
> In needed to auto-number the rows, and I created a sequence for that
> (thanks to a good advice on the GENERAL list).
>
> In Access, I know that I need good primary keys on my linked tables
> (integer, single column). I though that using this column as a primary
> key would be a good idea .
>
> The view looks like this :
> CREATE OR REPLACE VIEW my_view AS
> SELECT nextval('my_view_seq'::regclass)::integer AS "index", <many
> other fields> FROM <many tables>;
>
> Note I had to explicitely cast the sequence as integer, because it was
> detected as a decimal field in Access (equivalent to NUMERIC in
> PostgreSQL).
>
> In Access I link my tables via some VBA code. I have a routine that
> creates the primary keys when they are non-trivial. In this case, the
> routine executes "CREATE UNIQUE INDEX my_pkey ON TABLE my_linked_table
> (index)".
>
>
A unique index is not a primary key, you need to create the PK like this:
ALTER TABLE products ADD CONSTRAINT some_name PRIMARY KEY (product_no);
--Make sure you only use one column for the PK!!

What we have found is that Access displays the #deleted when the Primary
key is on more than one field and is not a integer value.
Our solution was to use a Primary key field as a recid using a sequence
(SERIAL type) and then to use a unique index on each column that would
have normally been in the Primary Key. Once we did this the #deleted
stuff went away.

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