On Oct 26, 1:46*pm, bala <balk...@gmail.com> wrote:
> On Oct 26, 3:01*am, akhosravi4...@hotmail.com wrote:
>
>
>
>
>
> > 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?
>
> *************************************************************
>
> 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 -
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?
|
Similar Threads
