1

I have been all over the web looking for a way to fix this, and all the answers seem to tell me that I am doing this correctly. I have inherited this Access / SQL Server database, and I cannot seem to get this piece to work.

I am executing a fairly basic SQL INSERT query that works fine. I have checked it several times. Once the query executes, I am attempting to get the identity value back out of it to use for the rest of my code.

Here is my code:

db.Execute (SQLStr)

Set rs = db.OpenRecordset("SELECT @@IDENTITY")
InNo = rs(0)
Debug.Print InNo
rs.Close
db.Close

This always returns a value of 0 for InNo. I have tried several different methods but I cannot seem to get this working.

I have also used this method from the SQL Server directly, and querying the @@Identity works fine.

Thanks in advance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
andrews
  • 13
  • 3
  • I would recommend to use **`SCOPE_IDENTITY()`** instead of anything else to grab the newly inserted identity value. [See this blog post for an explanation as to WHY](http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/) – marc_s Oct 07 '14 at 16:04
  • I believe you have to join the two SQL statements - they are executed in different contexts, so the second statement doesn't have the same scope. And yeah, use `SCOP_IDENTITY()` too. – Luaan Oct 07 '14 at 16:07
  • Instead of using pass through sql (which btw is highly likely to vulnerable to sql injection) you should create an insert stored procedure and include and OUTPUT parameter for SCOPE_IDENTITY. This gives you a lot more control over what is happening AND you will have parameterized queries instead of executing a string with user inputs. – Sean Lange Oct 07 '14 at 16:10
  • marc_s - I have read that, but have been unable to get that working as well. Do you have any code suggestions? – andrews Oct 07 '14 at 16:12
  • Sean - This is an internal database only, so I am not worried about security much (especially since 99% of my users are computer illiterate. ), but I will take a look at stored procedures. – andrews Oct 07 '14 at 16:13
  • Luaan - I have tried that, but Access does not allow you to join the statements and run them together at the same time. – andrews Oct 07 '14 at 16:14

1 Answers1

0

I'm suspecting that you are running SELECT @@IDENTITY on your local Access database, which of course doesn't know what @@IDENTITY is.

I'm not 100% sure, though, because you didn't show your whole code and I don't know what db is.
If db is CurrentDB, then the Set rs = db.OpenRecordset line opens a DAO.Recordset which does execute the query on your local Access database.

If my suspicion is correct, then you need to open the recordset where you execute SELECT @@IDENTITY via pass-through query, so it actually runs on the SQL Server.

Community
  • 1
  • 1
Christian Specht
  • 35,843
  • 15
  • 128
  • 182
  • Sorry, I was a little stingy on my copy paste. I am using currentdb, and all of my tables are linked tables from the SQL Server. – andrews Oct 07 '14 at 16:21
  • I thought so. So I was right - you need to execute `SELECT @@IDENTITY` via pass-through query, so it's actually executed by SQL Server, not by Access. – Christian Specht Oct 07 '14 at 16:22
  • That works. Had to go find out how to write a pass through query to do that, but it significantly cleaned up my code and it actually works now. – andrews Oct 08 '14 at 17:08