Using "SELECT SCOPE_IDENTITY()" in ADODB Recordset

Question

Using a VBA script in Excel, I'm trying to insert a new row into a table and then get back the identity value of that row. If I run:

INSERT INTO DataSheet(databaseUserID, currentTimestamp)
VALUES (1, CURRENT_TIMESTAMP);
SELECT SCOPE_IDENTITY()

in Management Studio, the row is inserted and it gives me the returned identity value as expected. However, when I run the exact same query through a ADODB recordset in VBA, I'm having trouble. The row is indeed inserted, but I can't access the identity value. The recordset lists 0 fields and has actually been closed as well. I've tried with and without the semicolon, and I also tried running the query as a single transaction as well. Same deal, no dice. Any idea what is going on?

Here's my VBA:

Dim rs As ADODB.Recordset
Dim cn As Connection
Dim SQLStr As String
Dim serverName As String
Dim databaseName As String

serverName = "MSSQLServer"
databaseName = "QA"
cxnStr = "Driver={SQL Server};Server=" & serverName & ";Database=" & databaseName & ";"

SQLStr = "INSERT INTO DataSheet(databaseUserID, currentTimestamp)
VALUES (1, CURRENT_TIMESTAMP); SELECT SCOPE_IDENTITY()"
Set cn = New ADODB.Connection
cn.Open cxnStr
Set rs = New ADODB.Recordset
rs.Open SQLStr, cn, adOpenKeyset, adLockOptimistic
MsgBox (rs.Fields(0).Value)

And the message box fails to display because the rs.Fields(0).Value returns NULL. I added a watch to rs, and, like I said, shows 0 fields after the query and also appears to be closed (state=0).

Answer 1

See what happens when you remove the adOpenKeySet and adLockOptimistic values leave them at their defaults.

Answer 2

In your rs.Open Try this

rs.Open SQLStr, cn, adCmdText

Answer 3

When you run a batch of commands using ADODB, I believe it runs each one seperately. To force the next command to run, you have to use the following:

Set rs = rs.NextRecordset()

Changing the end of your routine to the following should do the trick:

Set rs = New ADODB.Recordset
rs.Open SQLStr, cn, adOpenKeyset, adLockOptimistic
Set rs = rs.NextRecordset
MsgBox (rs.Fields(0).Value)

Answer 4

You are executing two statements so you will get two results back. the recordset object can only hold one result at a time - to get the other result you need to use the NextRecordset method.

Set rs = rs.NextRecordset