6

I'm wondering how I could get the most recently generated autonumber value from a table in another db. Currently I am doing this:

Do Until rsA.EOF
    'Inserts new row here (works)
    Set rs = New ADODB.Recordset 
    rs.Open "SELECT @@Identity" (Connection info)
    SQLcmd = "UPDATE tbl SET col = " & rs("SELECT @@Identity").Value & " 
    (WHERE statement);"
    DoCmd.RunSQL SQLcmd
    rsA.MoveNext
Loop

But its giving col a value of 0 instead of the newly generated autonumber. Any idea why? Or another way to do this?

HansUp
  • 95,961
  • 11
  • 77
  • 135
user2618908
  • 61
  • 5

1 Answers1

3

You didn't show the code which does the INSERT into the other database. If you're using the Execute method of an ADO Connection object to do that, run the SELECT @@Identity query from that same connection object ... not a new connection with the same connection string. @@Identity is only usable within the same connection session; otherwise you'll get 0.

And actually you don't even need a recordset to capture that value. If your connection object is named conn, this will return a recordset, but you need not assign it to a recordset object variable. Simply ask for the first item from the returned recordset. 

Debug.Print "most recent autonumber: " & _
    conn.Execute("SELECT @@Identity")(0)
HansUp
  • 95,961
  • 11
  • 77
  • 135
  • 1
    +1 Beat me to it. As @HansUp states, the key is to use the same *connection*, not simply the same connection string. – mwolfe02 Jul 29 '13 at 18:17
  • I'm using a SQL INSERT INTO SELECT statement. When I run conn.Execute(...) it returns 0. – user2618908 Jul 29 '13 at 18:22
  • That shouldn't be a problem if you're executing the `INSERT` from an ADO connection object, and you later `SELECT @@Identity` from that same connection object. The code in my answer was tested in Access 2007. I didn't show the `INSERT`, but I can if it helps. – HansUp Jul 29 '13 at 18:25
  • I was saving the command as a string and using runcmd.runSQL. Will that not work? – user2618908 Jul 29 '13 at 18:27
  • `RunSQL` does not use the ADO connection. Most likely you're getting 0 as the last identity value from the *current* database ... not from the other db you're inserting into. – HansUp Jul 29 '13 at 18:29
  • I've used the @@IDENTITY all the time too. Recently, one of my coworker told me that I should never use the @@IDENTITY. He said that I should instead used know values and do a select statement from the proper table. The @@IDENTITY has been working fine for me. Is it really bad to use the @@IDENTITY? – Josiane Ferice Apr 22 '14 at 18:25
  • This is a great single-line example of how to get @@Identity with ADO. The simple method for DAO is `CurrentDb.OpenRecordset("SELECT @@IDENTITY")(0)` – Ben Dec 09 '19 at 18:45