How to get id of newly inserted record using Excel VBA?

Question

Seems a common enough problem this, but most solutions refer to concatenating multiple SQL commands, something which I believe can't be done with ADO/VBA (I'll be glad to be shown wrong in this regard however).

I currently insert my new record then run a select query using (I hope) enough fields to guarantee that only the newly inserted record can be returned. My databases are rarely accessed by more than one person at a time (negligible risk of another insert happening between queries) and due to the structure of the tables, identifying the new record is normally pretty easy.

I'm now trying to update a table that does not have much scope for uniqueness, other than in the artificial primary key. This means there is a risk that the new record may not be unique, and I'm loathe to add a field just to force uniqueness.

What's the best way to insert a record into an Access table then query the new primary key from Excel in this situation?

Thanks for the replies. I have tried to get @@IDENTITY working, but this always returns 0 using the code below.

Private Sub getIdentityTest()
    Dim myRecordset As New ADODB.Recordset
    Dim SQL As String, SQL2 As String

    SQL = "INSERT INTO tblTasks (discipline,task,owner,unit,minutes) VALUES (""testDisc3-3"",""testTask"",""testOwner"",""testUnit"",1);"
    SQL2 = "SELECT @@identity AS NewID FROM tblTasks;"

    If databaseConnection Is Nothing Then
        createDBConnection
    End If

    With databaseConnection
        .Open dbConnectionString
        .Execute (SQL)
        .Close
    End With

    myRecordset.Open SQL2, dbConnectionString, adOpenStatic, adLockReadOnly

    Debug.Print myRecordset.Fields("NewID")

    myRecordset.Close

    Set myRecordset = Nothing
End Sub

Anything stand out being responsible?

However, given the caveats helpfully supplied by Renaud (below) there seems nearly as much risk with using @@IDENTITY as with any other method, so I've resorted to using SELECT MAX for now. For future reference though I would be interested to see what is wrong with my attempt above.

Answer 1

If the artificial key is an autonumber, you can use @@identity.

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.

DAO Example

'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")

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")

Answer 2

About your question:

I'm now trying to update a table that does not have much scope for uniqueness, other than in the artificial primary key. This means there is a risk that the new record may not be unique, and I'm loathe to add a field just to force uniqueness.

If you are using an AutoIncrement for your primary key, then you have uniqueness and you could use SELECT @@Identity; to get the value of the last autogenerated ID (see caveats below).

If you are not using autoincrement, and you are inserting the records from Access but you want to retrieve the last one from Excel:

• make sure your primary key is sortable, so you can get the last one using a query like either of these:

  SELECT MAX(MyPrimaryField) FROM MyTable;
  SELECT TOP 1 MyPrimaryField FROM MyTable ORDER BY MyPrimaryField DESC;
  

• or, if sorting your primary field wouldn't give you the last one, you would need to add a DateTime field (say InsertedDate) and save the current date and time every time you create a new record in that table so you could get the last one like this:

  SELECT TOP 1 MyPrimaryField FROM MyTable ORDER BY InsertedDate DESC;
  

In either of these cases, I think you would find adding an AutoIncrement primary key as being a lot easier to deal with:

• It's not going to cost you much

• It's going to guarantee you uniqueness of your records without having to think about it

• It's going to make it easier for you to pick the most recent record, either using @@Identity or through sorting by the primary key or getting the Max().

From Excel

To get the data into Excel, you have a couple of choices:

• create a data link using a query, so you can use the result directly in a Cell or a range.

• query from VBA:

  Sub GetLastPrimaryKey(PrimaryField as string, Table as string) as variant
      Dim con As String
      Dim rs As ADODB.Recordset
      Dim sql As String
      con = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source= ; C:\myDatabase.accdb"
      sql = "SELECT MAX([" & PrimaryField & "]) FROM [" & MyTable & "];"
      Set rs = New ADODB.Recordset
      rs.Open sql, con, adOpenStatic, adLockReadOnly
      GetLastPrimaryKey = rs.Fields(0).Value
      rs.Close
      Set rs = Nothing
  End Sub
  

Note about @@Identity

You have to be careful of the caveats when using @@Identity in standard Access databases(*):

• It only works with AutoIncrement Identity fields.

• It's only available if you use ADO and run SELECT @@IDENTITY;

• It returns the latest used counter, but that's for all tables. You can't use it to return the counter for a specific table in MS Access (as far as I know, if you specify a table using FROM mytable, it just gets ignored).
  In short, the value returned may not be at all the one you expect.

• You must query it straight after an INSERT to minimize the risk of getting a wrong answer.
  That means that if you are inserting your data at one time and need to get the last ID at another time (or another place), it won't work.

• Last but not least, the variable is set only when records are inserted through programming code.
  This means that is the record was added through the user interface, @@IDENTITY will not be set.

(*): just to be clear, @@IDENTITY behaves differently, and in a more predictive way, if you use ANSI-92 SQL mode for your database.
The issue though is that ANSI 92 has a slightly different syntax than the ANSI 89 flavour supported by Access and is meant to increase compatibility with SQL Server when Access is used as a front end.

Answer 3

Try following macro code.First add a command button to the sheet from the control box and paste following codes in the code window

Private Sub CommandButton1_Click()
    MsgBox GetLastPrimaryKey
End Sub

Private Function GetLastPrimaryKey() As String
Dim con As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
con = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myaccess.mdb;Persist Security Info=False"
sql = "SELECT MAX(id) FROM  tblMyTable"

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open con
rs.Open sql, cn, 3, 3, 1
If rs.RecordCount <> 0 Then
   GetLastPrimaryKey = rs.Fields(0).Value
End If
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Function

Chris

Convert your Excel spreadsheet into an online calculator.
http://www.spreadsheetconverter.com

Answer 4

I have tried to get @@IDENTITY working, but this always returns 0 using the code below.

Your code sends SQL and SQL2 through different connection objects. I don't think @@identity will return anything other than zero unless you ask from the same connection where you executed your INSERT statement.

Try changing this:

myRecordset.Open SQL2, dbConnectionString, adOpenStatic, adLockReadOnly

to:

myRecordset.Open SQL2, databaseConnection, adOpenStatic, adLockReadOnly