Excel VBA and ADODB: retrieve auto identity of row inserted into SQL Server from VBA

Question

In a VBA module for Excel. Using an ADODB.Connection and this connection string:

"Provider=SQLOLEDB;Data Source=MARS;Initial Catalog=automation;Trusted_connection=yes;"

I want to:

INSERT INTO test (data) VALUES ('Something')
Retrieve the auto incremented identity (test.data_id) of the newly inserted row.

Here the question that help you http://stackoverflow.com/questions/976593/how-do-i-return-a-new-identity-column-value-from-an-sqlserver-select-statement — Hiten004, Feb 10 '13 at 23:19
Thanks Hiten0004. I saw that one earlier. I can't get it to work in VBA. — user743094, Feb 10 '13 at 23:22

score 6 · Answer 1 · answered Feb 10 '13 at 23:39

6

Dim identity as integer
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

cn.ConnectionString = "whatever..."
cn.Open
cn.Execute ("INSERT INTO test(data) VALUES ('hello world')")
rs.Open "SELECT @@identity AS NewID", cn
identity = rs.Fields("NewID") 
MsgBox (identity)

answered Feb 10 '13 at 23:39

user743094

329
1
6
19

Awesome , thanks just what I was looking for , worked like magic – spacemonkey Oct 14 '16 at 14:46
2

If you have a connection variable this can be simplified as `identity = cn.Execute("SELECT @@Identity")(0)` without creating a recordset. – Ben Dec 10 '19 at 04:56

Excel VBA and ADODB: retrieve auto identity of row inserted into SQL Server from VBA

1 Answers1