Oracle and MS-Access sequence problems -Reply

I use triggers on Oracle tables to automatically assign primary keys from a sequence. No access intervention required (just leave the pk field blank in access).

BUT, in some cases, this causes access to show the record you just typed in as "#DELETED". The record is there, but Access has lost it because Access doesn't know the primary key assigned by the Oracle trigger.

To get around this problem, you can use Access basic to pre-fetch the next sequence number and insert it into the pk field when a form opens in "new" mode. You must use a pass through query to fetch the nextval. And of course you must modify the trigger for the table so that it doesn't overwrite the pre-fetched primary key.

Sample code for pass through queries, using basic and queries, and form open events are included in MS Access help files.

Andy
Andrew McAllister -- Programmer Analyst II Office of Research, University of
Missouri-Columbia
mcallister_at_grad.missouri.edu Voice: (314) 884-6237

>>> Thiele, Dave <dthiele_at_EMS1.UWSP.EDU> 01/18/96
03:27pm >>>
snip
We want to be able to use Oracle's Sequence generator to create unique ID's on row-inserts, but have been unable to get MS-Access = to recognize any way to accomplish this - it doesn't recognize the SQL state= ment with the
"<sequence>.nextval" in it.
snip Received on Fri Jan 19 1996 - 10:43:13 CST