- 'Reference: Microsoft DAO 3.6 Object Library '
- Dim db As DAO.Database
- Dim rs As DAO.Recordset
- Set db = CurrentDb
- db.Execute ("INSERT INTO table1 (field1, Crdate ) " _
- & "VALUES ( 46, #" & Format(Date, "yyyy/mm/dd") & "#)")
- Debug.Print db.RecordsAffected
- Set rs = db.OpenRecordset("SELECT @@identity AS NewID FROM table1")
- Debug.Print rs.Fields("NewID")
Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
Get the ID of a Newly Inserted Record
From Wiki
Note that with both these examples, the transaction is isolated from other events, so the identity returned is the one just inserted. You can test this by pausing the code at Debug.Print db.RecordsAffected or Debug.Print lngRecs and inserting a record manually into table1, continue the code and note that the identity returned is not that of the record inserted manually, but of the previous record inserted by code. This is not the case with SQL Server, where @@Identity should only be used with great care: Identity Values And Triggers
DAO Example
ADO Example
- Dim cn As New ADODB.Connection
- Dim rs As New ADODB.Recordset
- Set cn = CurrentProject.Connection
- cn.Execute ("INSERT INTO table1 (field1, Crdate ) " _
- & "VALUES ( 46, #" & Format(Date, "yyyy/mm/dd") & "#)"), lngRecs
- Debug.Print lngRecs
- rs.Open "SELECT @@identity AS NewID FROM table1", cn
- Debug.Print rs.Fields("NewID")
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.