Return results of INSERT

[Brian]

I have a process that needs to first insert a header record in one table,
then detail records in another table where the newly-created PK in the header
table is the FK for the detail records.

My concern is capturing the auto-numbered PK of the just-inserted record so
that I can include it as the FK when I insert the detail records. As of now,
I insert the header record and immediately capture the DMax of the header
record PK to a variable, then pass that to the INSERT statement for the
detail records. Given that this takes all of perhaps 1/10 second, I suppose
there is little practical concern that another user may insert a header
record between the time I insert the header & capture the DMax, but is there
a way to actually capture the result of an INSERT statement and identify with
absolute certainty the record just inserted?

At first glance, the simple solution might appear to be binding the form to
the table and navigating to a new record. However, that would be very
difficult in this case, because the form in question is used to run perhaps
15 similar processes affecting different table combinations. It is a generic
form for processing various types of batch processes - the user selects which
batch process is to be run from a combo box, and all the updates/inserts are
done in VBA.

I suppose I could use a generic hidden control and set its RecordSource,
along with the form's RecordSource, at runtime.

Any other way?

 

[RoyVidar]

My concern is capturing the auto-numbered PK of the just-inserted
record so that I can include it as the FK when I insert the detail
records. As of now, I insert the header record and immediately
capture the DMax of the header record PK to a variable, then pass
that to the INSERT statement for the detail records. Given that this
takes all of perhaps 1/10 second, I suppose there is little
practical concern that another user may insert a header record
between the time I insert the header & capture the DMax, but is there
a way to actually capture the result of an INSERT statement and
identify with absolute certainty the record just inserted?

Using ADO, you can do something like this to capture the last inserted
Autonumber value:

Dim rs as ADODB.Recordset
Dim cn as ADODB.Connection

Set cn = CurrentProject.Connection ' or any valid ADO connection
cn.Execute <TheSQL>
set rs = cn.Execute("SELECT @@Identity()",,adCmdText)
Debug.Print "Last identity: " & rs.fields(0).value

There's bound to be a DAO way to do the same, but for ADO, the idea
is that when you fetch the identity on the same connection that the
insert is executed on, you'll get the last identity.

Note - if you're using SQL server, you might be interested in
Scope_Identity() in stead.

 

[Brian]

This is just a split Access FE/BE. Are you saying that ADO will identify the
last record inserted within the current session? As of now, I just do this:

Dim strSQL as String
Dim PK as Long
strSQL = "INSERT into MyTable...blah, blah, blah")
CurrentDb.Execute strSQLRun, dbFailOnError
PK = DMax("[PK]","[MyTable]")

PK is the autonumbered PK of MyTable. I am just trying to make absolutely
certain that it somehow does not see the newly-inserted record or that
another user does not simultaneously do the same thing. Given the fact that
the entire process takes a few milliseconds, the DMax will PROBABLY never
grab a wrong PK - I just do not entirely trust "probably"...

 

[RoyVidar]

This is just a split Access FE/BE. Are you saying that ADO will
identify the last record inserted within the current session? As of
now, I just do this:

Dim strSQL as String
Dim PK as Long
strSQL = "INSERT into MyTable...blah, blah, blah")
CurrentDb.Execute strSQLRun, dbFailOnError
PK = DMax("[PK]","[MyTable]")

PK is the autonumbered PK of MyTable. I am just trying to make
absolutely certain that it somehow does not see the newly-inserted
record or that another user does not simultaneously do the same
thing. Given the fact that the entire process takes a few
milliseconds, the DMax will PROBABLY never grab a wrong PK - I just
do not entirely trust "probably"...

Using ADO, you can do something like this to capture the last
inserted Autonumber value:

Dim rs as ADODB.Recordset
Dim cn as ADODB.Connection

Set cn = CurrentProject.Connection ' or any valid ADO connection
cn.Execute <TheSQL>
set rs = cn.Execute("SELECT @@Identity()",,adCmdText)
Debug.Print "Last identity: " & rs.fields(0).value

There's bound to be a DAO way to do the same, but for ADO, the idea
is that when you fetch the identity on the same connection that the
insert is executed on, you'll get the last identity.

Note - if you're using SQL server, you might be interested in
Scope_Identity() in stead.

Using ADO, you can use something like my above sample to capture,
fetch, retrieve, get... the last Autonumber (Identity) "issued" on
that connection, yes. It will not grab an autonumber created through
another process.

 

[Brian]

Thank you. That should work nicely.

This is just a split Access FE/BE. Are you saying that ADO will
identify the last record inserted within the current session? As of
now, I just do this:

Dim strSQL as String
Dim PK as Long
strSQL = "INSERT into MyTable...blah, blah, blah")
CurrentDb.Execute strSQLRun, dbFailOnError
PK = DMax("[PK]","[MyTable]")

PK is the autonumbered PK of MyTable. I am just trying to make
absolutely certain that it somehow does not see the newly-inserted
record or that another user does not simultaneously do the same
thing. Given the fact that the entire process takes a few
milliseconds, the DMax will PROBABLY never grab a wrong PK - I just
do not entirely trust "probably"...

Brian wrote:

My concern is capturing the auto-numbered PK of the just-inserted
record so that I can include it as the FK when I insert the detail
records. As of now, I insert the header record and immediately
capture the DMax of the header record PK to a variable, then pass
that to the INSERT statement for the detail records. Given that
this takes all of perhaps 1/10 second, I suppose there is little
practical concern that another user may insert a header record
between the time I insert the header & capture the DMax, but is
there a way to actually capture the result of an INSERT statement
and identify with absolute certainty the record just inserted?

Using ADO, you can do something like this to capture the last
inserted Autonumber value:

Dim rs as ADODB.Recordset
Dim cn as ADODB.Connection

Set cn = CurrentProject.Connection ' or any valid ADO connection
cn.Execute <TheSQL>
set rs = cn.Execute("SELECT @@Identity()",,adCmdText)
Debug.Print "Last identity: " & rs.fields(0).value

There's bound to be a DAO way to do the same, but for ADO, the idea
is that when you fetch the identity on the same connection that the
insert is executed on, you'll get the last identity.

Note - if you're using SQL server, you might be interested in
Scope_Identity() in stead.

Using ADO, you can use something like my above sample to capture,
fetch, retrieve, get... the last Autonumber (Identity) "issued" on
that connection, yes. It will not grab an autonumber created through
another process.

 

[David H]

The following code in DAO inserts a record directly to the table and captures
the PK of the newly added record.

Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("tblTrailerActivityDetailShows")
rs.AddNew
rs("lngTrailerActivityHeaderId") = lngExistingHeader
rs("txtShowNumber") = Me.pg2_cboSelectShow
rs.Update
varNewRecordId = rs("lngTrailerActivityDetailId")
rs.Close
Set rs = Nothing
Set db = Nothing

Thank you. That should work nicely.

This is just a split Access FE/BE. Are you saying that ADO will
identify the last record inserted within the current session? As of
now, I just do this:

Dim strSQL as String
Dim PK as Long
strSQL = "INSERT into MyTable...blah, blah, blah")
CurrentDb.Execute strSQLRun, dbFailOnError
PK = DMax("[PK]","[MyTable]")

PK is the autonumbered PK of MyTable. I am just trying to make
absolutely certain that it somehow does not see the newly-inserted
record or that another user does not simultaneously do the same
thing. Given the fact that the entire process takes a few
milliseconds, the DMax will PROBABLY never grab a wrong PK - I just
do not entirely trust "probably"...

:

Brian wrote:

My concern is capturing the auto-numbered PK of the just-inserted
record so that I can include it as the FK when I insert the detail
records. As of now, I insert the header record and immediately
capture the DMax of the header record PK to a variable, then pass
that to the INSERT statement for the detail records. Given that
this takes all of perhaps 1/10 second, I suppose there is little
practical concern that another user may insert a header record
between the time I insert the header & capture the DMax, but is
there a way to actually capture the result of an INSERT statement
and identify with absolute certainty the record just inserted?

Using ADO, you can do something like this to capture the last
inserted Autonumber value:

Dim rs as ADODB.Recordset
Dim cn as ADODB.Connection

Set cn = CurrentProject.Connection ' or any valid ADO connection
cn.Execute <TheSQL>
set rs = cn.Execute("SELECT @@Identity()",,adCmdText)
Debug.Print "Last identity: " & rs.fields(0).value

There's bound to be a DAO way to do the same, but for ADO, the idea
is that when you fetch the identity on the same connection that the
insert is executed on, you'll get the last identity.

Note - if you're using SQL server, you might be interested in
Scope_Identity() in stead.

Using ADO, you can use something like my above sample to capture,
fetch, retrieve, get... the last Autonumber (Identity) "issued" on
that connection, yes. It will not grab an autonumber created through
another process.