Capture ID of newly inserted row

  • Thread starter Thread starter akhosravi4703
  • Start date Start date
A

akhosravi4703

I have an access database that I am scaling up to an Oracle database
via ODBC connection. I've run into a problem capturing the ID column
of a newly created record. I need this ID to populate the FK of
dependent related rows in another table that I am generating at the
same time.

I used to use the following code when using just Access in the past

Dim db as database, rst as recordset, x as long
Set db = currentdb
set rst = db.openrecordset("SELECT * FROM tblMyParentTable")
rst.addnew
rst!Field1 = value1
rst!Field2 = value2
x = rst!IdentityField
rst.update

set rst = db.openrecordset("SELECT * FROM tblMyChildTable")
rst.addnew
rst!FK = X
rst!FieldA = valueA
rst.update

Worked fine so long as you set the X variable before the update
command because Access reserves the new auto number before insertion.
However, this doesn't work with Oracle as the sequence is apparently
not generated until after insertion.

I've been searching all night for the answer, but it seems everything
on the web tells you how to do this when using SQLServer with the
@@Identity method, which won’t work here. I've seen only vague answers
to this with Oracle.

Here's the rub. It will not be practical to write any triggers or
stored procedures within Oracle to do this. I don't have privileges to
do that, nor the time to wait for a DBA to do it. Is there some way I
can do this without cheese balling it using some flavor of max of
identity column?
 
I have an access database that I am scaling up to an Oracle database
via ODBC connection. I've run into a problem capturing the ID column
of a newly created record. I need this ID to populate the FK of
dependent related rows in another table that I am generating at the
same time.

I used to use the following code when using just Access in the past

Dim db as database, rst as recordset, x as long
Set db = currentdb
set rst = db.openrecordset("SELECT * FROM tblMyParentTable")
rst.addnew
rst!Field1 = value1
rst!Field2 = value2
x = rst!IdentityField
rst.update

set rst = db.openrecordset("SELECT * FROM tblMyChildTable")
rst.addnew
rst!FK = X
rst!FieldA = valueA
rst.update

Worked fine so long as you set the X variable before the update
command because Access reserves the new auto number before insertion.
However, this doesn't work with Oracle as the sequence is apparently
not generated until after insertion.

I've been searching all night for the answer, but it seems everything
on the web tells you how to do this when using SQLServer with the
@@Identity method, which won’t work here. I've seen only vague answers
to this with Oracle.

Here's the rub. It will not be practical to write any triggers or
stored procedures within Oracle to do this. I don't have privileges to
do that, nor the time to wait for a DBA to do it. Is there some way I
can do this without cheese balling it using some flavor of max of
identity column?
Click to expand...

*************************************************************

Save the record through Code. On the click of the Save Button first
save the Parent record. You will have the ID (Primary Key) of the
Parent Record. Now save the detail records including the foreign key
(Primary key of Parent record)
 
*************************************************************

Save the record through Code. On the click of the Save Button first
save the Parent record. You will have the ID (Primary Key) of the
Parent Record. Now save the detail records including the foreign key
(Primary key of Parent record)- Hide quoted text -

- Show quoted text -
Click to expand...

You're losing me on the "You will have the ID (Primary Key)" part?
From what I can tell, any new record added must be fully committed and
the datasource requeried before it will yield up what the newly
generated pk was. Even the .LastModified method won't work. Oh, the
record is added to the recordset, but if you navigate to that newly
added record in the recordset and try to extract what the PK is,
you'll get a runtime error that the record was deleted (well, not
deleted, but not completely there either).

At this point I can only see two possibilities. Either I have to write
a stored procedure to create these two records and call it via a
passthrough or I need to write the parent record, run a completely new
search to uniquely identify what that new record was to capture its
PK, then add the child record. I was trying to avoid that middle step
as I view that middle query as wasteful - the database is already slow
enough as it is. Any other thoughts on this?
 
We used a table of "next unique ids" for the tables we updated with some
server DBs (though not Oracle) along with a stored procedure accessed via a
passthrough query. It would retrieve and update the "next unique id"
recorded in the table, and return it, rather than using the server
equivalent of AutoNumber. Thus, whenever we added the new record, we
already had its unique id.
 
Back
Top