SCOPE_IDENTITY returns null until an error is thrown in MS Access

Question

MS Access front-end with a SQL Server back-end, using pass-through queries in Access over OBDC connection. No ADO, just simple DAO query objects.

After an INSERT query is executed, a "SELECT SCOPE_IDENTITY()" query is run to retrieve the ID of the newly-inserted record.

The SCOPE_IDENTITY query returns null--unless an error is thrown in Access. Regardless what type of error it is, if an error is thrown and I end it, then every time this code is run afterward--until the form is closed or the app is closed (depending on whether the code is in the form or a module)--it returns the correct value after each insert.

If I add error handling or change the code to prevent errors, then it continues to return null every time the code is executed.

I verified the INSERT is successful each time, even when SCOPE_IDENTITY returns null.

Trying to figure out what it is about an Access application error that would make SCOPE_IDENTITY work correctly on all subsequent executions?

Code:

Sub test()
Dim strConnect As String
Dim qdf As QueryDef
Dim newID As Long
strConnect = "ODBC;Description=myServer;DRIVER=SQL Server;SERVER=serverpath;Trusted_Connection=Yes;DATABASE=Apps"

'Insert
Set qdf = CurrentDb.CreateQueryDef("")
qdf.Connect = strConnect
qdf.SQL = "INSERT INTO myTable ([Field1],[Field2]) VALUES ('Value1','Value2')"
qdf.ReturnsRecords = False
qdf.Execute

'Get new record ID
Set qdf = CurrentDb.CreateQueryDef("")
qdf.Connect = strConnect
qdf.SQL = "SELECT SCOPE_IDENTITY()"
qdf.ReturnsRecords = True
newID = qdf.OpenRecordset(dbOpenSnapshot)(0)

Debug.Print "New ID:" & newID

End Sub

The first time this runs, SCOPE_IDENTITY returns null, so Access throws an error because the "newID" is dimmed as Long so doesn't accept null values. But on all subsequent executions, SCOPE_IDENTITY returns the correct value.

If I dim "newID" as a Variant type so it will accept null, no error is thrown and SCOPE_IDENTITY will return null every time this is executed.

I get the same behavior using @@IDENTITY.

Answer 1

What you have should work. Even if you re-create the connection, then your code should work. I have to guess/think/speculate that the first query on additional executes is suffering some error - and you have say a on-error resume next.

If you have a on-error resume next perhaps before the posted code, then perhaps that is the issue. Perhaps you attempt to insert say a null value, when nulls are not allowed.

I mean, if this is on the same table, and the same code snip? Hum, I am at a loss as to why this is not working.

I would double check that some on error resume is not active. Perhaps put a on error goto 0 to turn off the error resume next.

Answer 2

Sorry all, turns out the above code does work fine. I pulled it into a fresh database with nothing else and it ran fine the first time and every time after. (I know--should've done that before posting...(facepalm)

So there is something else in the original app interfering I'll need to track down. On startup, the app opens several other recordsets from the server so it looks like there's something there that's interfering, since an application error would destroy all recordset variables and close any connections that might still be open, removing the interference and allowing the subsequent executions to work fine.