Skip site navigation (1) Skip section navigation (2)

Access2000 & sequence as primary key in view : #DELETED

From: Arnaud Lesauvage <thewild(at)freesurf(dot)fr>
To: pgsql-odbc(at)postgresql(dot)org
Subject: Access2000 & sequence as primary key in view : #DELETED
Date: 2006-10-11 08:43:46
Message-ID: 452CAEC2.5070903@freesurf.fr (view raw or flat)
Thread:
Lists: pgsql-odbc
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

Responses

pgsql-odbc by date

Next:From: noreplyDate: 2006-10-11 08:45:44
Subject: [ psqlodbc-Bugs-1000749 ] Crashes when using SSL with 8.02.0002 and .0100
Previous:From: Dave PageDate: 2006-10-11 07:23:58
Subject: Re: New release of psqODBC?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group