-1

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.

JoshL
  • 1
  • 1
  • Without any code, we're going to be guessing at possible causes. Have you reviewed the causes identified here, and other methods of retrieving the newly inserted PK? https://stackoverflow.com/questions/2389354/how-can-scope-identity-return-null-when-identity-does-not – CoffeeNeedCoffee Nov 13 '20 at 22:38
  • Does this answer your question? [Get ID of Last Inserted Record - Access DAO, ODBC, SQL Server 2008 Identity Field](https://stackoverflow.com/questions/10905596/get-id-of-last-inserted-record-access-dao-odbc-sql-server-2008-identity-fiel) – Robert Harvey Nov 13 '20 at 23:26
  • @CoffeeNeedCoffee: Sorry, added the sample code. Was hoping maybe it was a simple answer.... :) – JoshL Nov 13 '20 at 23:40
  • @RobertHarvey: Thanks for the link, but in that article, they were using linked tables. I'm using pass-through queries instead of linked tables. – JoshL Nov 13 '20 at 23:40
  • @CoffeeNeedCoffee: I have reviewed that article but didn't find anything that fit. And some of the article links in it are dead. Forgot to mention I get the same behavior with @@IDENTITY. The method I'm using is working some of the time. – JoshL Nov 13 '20 at 23:50
  • 1
    No Access expert here, but it looks like you're executing `SELECT SCOPE_IDENTITY()` on a different SQL connection than where the record insert occurred. That would be why it's returning NULL. Are you able to combine the two statements into a single query, e.g.: `INSERT INTO myTable ([Field1],[Field2]) VALUES ('Value1','Value2'); SELECT SCOPE_IDENTITY() as NewID;`? – AlwaysLearning Nov 14 '20 at 00:40
  • @alwayslearning Access can't execute a combined statement like that with the built-in DAO. Can do that with ADO, but trying to avoid it in this case. I thought the same thing about the connection difference which I'm suspecting may be at the heart of this somehow. But since this is working fine on subsequent executions after an initial run with an error, I'm not sure. – JoshL Nov 14 '20 at 02:54

2 Answers2

0

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.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
  • Odd thing is that it does work fine as it is--the NEXT time it's run--only if there IS an error the first time. I've tested by putting this sub into a module and running it first thing before running any other code and it behaves the same way. – JoshL Nov 14 '20 at 02:49
0

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.

JoshL
  • 1
  • 1