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

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)".

The primary key is created OK, the column is recognized as
integer-type in Access, but when I open the table all the fields are
marked as #DELETED.

Where exactly is the problem here ?
Is it just impossible to use this sequence-generated field as a
primary key in Access ?

Thanks for your help on this !

--
Arnaud

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

>
> 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

Tony Caduto a écrit :
> 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).

The fact is that we are talking about ODBC here.
The difference between a table and a view is that in the table one
record as one value of the sequence, forever. With a linked view
in access, if I sort the table or query it in anyway new sequence
numbers are given to all the fields.

> Here is a example table that at one time always got the # deleted in
> Access 97, then we modified it like so:
> ...
> > 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.

I know about all these problems with Access, and all my tables are
configured in such a way that the #DELETED problem never arises.
The problem is with this view. (sorry to insist, but it really
does matter here that the linked table is a view)

>
> The fact is that we are talking about ODBC here.
> The difference between a table and a view is that in the table one
> record as one value of the sequence, forever. With a linked view in
> access, if I sort the table or query it in anyway new sequence numbers
> are given to all the fields.
>

I really don't see how that is possible. A Postgresql sequence when
used in a Serial Type only generates a value on a insert.
I have tons of views that reference tables with columns that are serial
and they don't regenerate themselves when I sort the table in Access.
Not to mention PG views are read only ( unless you have some rules
defined) The only problem we have had with sorting in access is with
varchars used as function OUT params which Access thinks are memo fields
and we got around that using views that cast them to varchars with lengths.

You have me totally lost as to the problem you are really having :-)

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

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>;

Though I don't understand the problem wholly, the virtual column "index"
doesn't seem appropriate for the identifier. How do you find the row
using the "index" ?

regards,
Hiroshi Inoue

Hiroshi Inoue a écrit :
> Though I don't understand the problem wholly, the virtual column "index"
> doesn't seem appropriate for the identifier. How do you find the row
> using the "index" ?

In Access, "index" is fine as a field identifier. You just have to
reference it between brackets : [index].

Arnaud Lesauvage wrote:
> Hiroshi Inoue a écrit :
>> Though I don't understand the problem wholly, the virtual column "index"
>> doesn't seem appropriate for the identifier. How do you find the row
>> using the "index" ?
>
> In Access, "index" is fine as a field identifier. You just have to
> reference it between brackets : [index].

What I meant is, for example

1. Issue the query "select * from my_view".
2. You may get some data whose "index" = 1, 2, 3, .....
3. Then issue the query "select * from my_view where index = 1"
4. The query would return no data.

regards,
Hiroshi Inoue

Hiroshi Inoue a écrit :
> Arnaud Lesauvage wrote:
>> Hiroshi Inoue a écrit :
>>> Though I don't understand the problem wholly, the virtual column "index"
>>> doesn't seem appropriate for the identifier. How do you find the row
>>> using the "index" ?
>>
>> In Access, "index" is fine as a field identifier. You just have to
>> reference it between brackets : [index].
>
> What I meant is, for example
>
> 1. Issue the query "select * from my_view".
> 2. You may get some data whose "index" = 1, 2, 3, .....
> 3. Then issue the query "select * from my_view where index = 1"
> 4. The query would return no data.

This is absolutely true, but actually this field is not really meant
to be used as a primary key. I need it because this view is the base
table of a csv export (and I need a unique "index" field in this export).
I chose it as the primary key in access only because I had no other
"correct" field (integer, unique), and I know that I need this kind of
field for a linked table to work correctly in Access.
Now I think that this #DELETED probleme does come from the fact that
the index changes whenever you query the view.
For the moment, I use the view's "real" primary key in Access and it
works OK (it is a 2 columns primary key though, but on two integer
columns).