get last insert id

[sted69]

Hello,

I need to get the last insert id from a recordset, similar to the mysql function "mysql_insert_id".

Getting it with a SELECT MAX(id) query won't work because the table will be edited by multiple users.

Any help would be great,
thanks in advance,

Stephen Edwards.

 

[pbaldy]

http://www.access-programmers.co.uk/forums/showthread.php?t=168926

 

[sted69]

Will this work for multiple access?

http://www.access-programmers.co.uk/forums/showthread.php?t=168926

 

[pbaldy]

Used correctly, the method posted by Wazz or methods using @@Identity will work in multiuser environments.

 

[neoPulse]

Hi I think I did it, try this

Dim dbs As Database
Dim rst As Recordset

Set dbs = Application.CurrentDb
Set rst = dbs.OpenRecordset("eventos")

With rst
.AddNew
!id_instituc = Parent.Form!id
!ano = Parent.Form.eltab.SelectedItem
!tipo_evento = cboPorDesarr
.Update
.MoveLast 'In this point i move to the last record
newEvento = !id
.Close
End With

if you have the recordset order by another key and the last record are not the recently one try this
..........
dim bkm as variant
With rst
.AddNew
!id_instituc = Parent.Form!id
!ano = Parent.Form.eltab.SelectedItem
!tipo_evento = cboPorDesarr
bkm = .bookmark
.Update
.Move bkm 'In this point i move to the bookmark in the last added record
newEvento = !id
.Close
End With

Excuse my English please

 

[evanscamman]

You can do it easily by using a recordset instead of a query to add the record.

dim lngNewID as Long
dim rs as DAO.Recordset
 
set rs = currentdb.openrecordset("MyTable")
rs.AddNew
 
lngNewID = rs!MyTableID
'Add other fields
rs.Update
rs.Close
set rs = nothing

Remember to grab the Record ID before the update command, as this moves the cursor.

Evan

 

[datAdrenaline]

>> Remember to grab the Record ID before the update command, as this moves the cursor <<

Just a point of note, on this important, and accurate, comment. While it is quite valid to use Evan's approach when JET|ACE (ie: mdb|accdb) files are the back end, it is not so good if the back end is something like SQL Server. In SQL Server, the autoid's are assigned right before record commital, so the new record pointer has a Null autoid upon new record instantiation ... I am NOT trying to discourage Evan's method, but give awareness that a different backend MAY behave differently.

The .MoveLast after the .Update, then reading the ID is a valid method for any back end if using a linked table object for the source of your DAO recordset. Also note that in and ADO recordset (IIRC) the cursor is left on the new record, so a movelast is not required.

Also ... neoPulse said ..

>> if you have the recordset order by another key and the last record are not the recently one try this <<

The new record will be the last record, unless you requery the recordset.

What I often do (if the situation allows) when adding records through a Recordset object is open the recordset to an empty recordset, that way I know that any records in the recordset are the ones I've added ...

Set dbs = Application.CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM eventos WHERE 1=0")

With rst
    .AddNew
    !id_instituc = Parent.Form!id
    !ano = Parent.Form.eltab.SelectedItem
    !tipo_evento = cboPorDesarr
    .Update
    .MoveLast 
    newEvento = !id
.Close
End With

 

[evanscamman]

Brent - thanks for the clarification. I have only used MDB - never SQL server yet. Theoretically, in a multi-user environment, when you use .UPDATE then .MOVELAST, isn't there a chance that the last record could have just been added by another user?

Evan

 

[datAdrenaline]

>> isn't there a chance that the last record could have just been added by another user?
<<
Only if your recordset is an dbOpenTable recordset, which is the default if it can be obtained (ie: Local Table as the source of the .OpenRecordset). Otherwise the only way to see additions made by others is to requery the recordset object. I generally only use SQL Statements in my .OpenRecordset calls, which default to a dbOpenDynaset, so I slip up and forget the disclaimer about dbOpenTable.

So ... just in case, and probably a best practice, it would be best to ensure the recordset is NOT opened in dbOpenTable (or generically known as Table Direct) mode ...

Set rst = dbs.OpenRecordset("eventos", dbOpenDynaset)

{note: in ADO the equivalent to dbOpenDynaset is adOpenKeyset ...}