Getting the key ID from a newly inserted record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What is the best way to get the value from an autonumber field of the new
record when you do a SQL Insert in Access VBA?
 
If you are a single user then you can use the Dmax to get the last autonumber
=dmax("AutoFieldName","TableName")

If multi user, try and find a unique value that you inserted to the table
and add a filter to the dmax
=dmax("AutoFieldName","TableName", "FieldToFilterOn = " & Value) 'For Number
=dmax("AutoFieldName","TableName", "FieldToFilterOn = '" & Value & "'") 'For
String
=dmax("AutoFieldName","TableName", "FieldToFilterOn = #" & Value & "#") 'For
Date
 
Ofer said:
If you are a single user then you can use the Dmax to get the last
autonumber =dmax("AutoFieldName","TableName")
Click to expand...

Note that this will only work if you are using *consecutive* autonumbers
(not random ones) and no other user has inserted a record between your
insert and the time you call DMax().

You can open a recordset on the SQL statement,

SELECT @@IDENTITY

and use the value it returns, provided you use the same connection both
to insert the record and open the recordset.
If multi user, try and find a unique value that you inserted to the
table and add a filter to the dmax
=dmax("AutoFieldName","TableName", "FieldToFilterOn = " & Value) 'For
Number =dmax("AutoFieldName","TableName", "FieldToFilterOn = '" &
Value & "'") 'For String
=dmax("AutoFieldName","TableName", "FieldToFilterOn = #" & Value &
"#") 'For Date
Click to expand...

This is a good approach, though you can use DLookup instead of DMax,
since we're assuming you have a unique value.

When I'm going to need the autonumber for a record I'm about to insert,
I generally don't use SQL to insert it. Instead, I open a recordset,
use the recordset's AddNew and Update methods to add the record, and get
the autonumber from the recordset before I close it.
 
What is the best way to get the value from an autonumber field of the
new record when you do a SQL Insert in Access VBA?
Click to expand...

You can't do this reliably.

@@IDENTITY will return the most recent autonumber so you can be fairly
sure that it belongs to the insert you think it should, as long as no
other process is going on at the same time (Form_Error events, timer
events, etc).

In a multi-user system, then you can't guarantee that another user won't
have inserted a record after your insert but before you reading the
IDENTITY value.

The one safe way is to use DAO:

' create an empty recordset; we don't need to lock the other
' records from other users
jetSQL = "SELECT MyAutoNum FROM MyTable WHERE FALSE"

' open the recordset; use a Dynaset because we have to write
' to it.
Set rs = db.OpenRecordset(jetSQL, dbOpenDynaset)

' create the new record
rs.AddNew
' enters the record and trigger the autonumber
' put in any required fields without default values
' here too
rs.Update

' recover the autonumber itself
MyNewIdentityNumber = rs!MyAutoNum

' tidy up
rs.Close

' and do the rest of the insert as an update
jetSQL = "UPDATE MyTable SET " & _
"MyField = """ & uSomeValue " & """, " & _
"MyOtherField = " & Format(SomeOtherValue, "0") & " " & _
"WHERE MyAutoNum = " & Format(MyNewIdentityNumber, "0")

' check it out
debug.asssert vbYes = MsgBox(jetSQL, vbYesNo, "Is this OK?")

' and do it
db.Execute jetSQL, dbFailOnError

Hope that helps


Tim F
 
Tim Ferguson said:
You can't do this reliably.

@@IDENTITY will return the most recent autonumber so you can be fairly
sure that it belongs to the insert you think it should, as long as no
other process is going on at the same time (Form_Error events, timer
events, etc).

In a multi-user system, then you can't guarantee that another user
won't have inserted a record after your insert but before you reading
the IDENTITY value.
Click to expand...

I think @@IDENTITY is connection-specific, so that users inserting
records on other connections won't affect the value returned for the
current connection. I could be wrong about that, but that's what I read
in ...

http://support.microsoft.com/kb/q232144/
INFO: Jet OLE DB Provider Version 4.0 Supports SELECT @@Identity

.... and what my quick experiments seem to show. On the other hand, this
means that the same connection must be used both to append the record
and to select @@IDENTITY.
 
I think @@IDENTITY is connection-specific, so that users inserting
records on other connections won't affect the value returned for the
current connection. I could be wrong about that, but that's what I
read in ...

http://support.microsoft.com/kb/q232144/ INFO: Jet OLE DB Provider
Version 4.0 Supports SELECT @@Identity

... and what my quick experiments seem to show.
Click to expand...


My understanding was the other way round, but I know far better than to
disagree! Thanks for the update.

All the best


Tim F
 
Tim Ferguson said:
My understanding was the other way round, but I know far better than
to disagree! Thanks for the update.
Click to expand...

I did say I'm not sure, so I wouldn't be *too* quick to change your mind
if I were you. And maybe it has changed with Jet updates. But I would
say that it's a point worth checking into, if it comes up again.
 
I did say I'm not sure, so I wouldn't be *too* quick to change your mind
if I were you. And maybe it has changed with Jet updates. But I would
say that it's a point worth checking into, if it comes up again.
Click to expand...

I'm still on MSDE 1.1 (=SQL Server 8) -- the web pages on (at least) the
newer versions suggest you are right on current technology.

All the best


Tim F
 
Back
Top