What is the equivalent to SQL Servers SCOPE_IDENTITY()?

I am building a class that has to deal with MS Access.

A vast majority of the code is working.

One of the methods is GetIdentity().

This uses the SQL statement "SELECT SCOPE_IDENTITY() AS lastID;" and returns the ID of the last row inserted.

What is the MS Access equivalent. I understand that MS Access does not require a primary / unique key, so I don't know what to do.

If the table has no uniqueness then false is a valid return value for me.

The answer must be via SQL as I'm NOT using ADO/DAO/etc to communicate with the databases.
LVL 40
Richard QuadlingSenior Software DeveloperAsked:
Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
If you use @@Identity, the tables will have to have a Primary Key column declared ... I believe @@Identity will return the value last inserted in a PK column, regardless of whether it's a AutoNumber field or not.

I'm sure you know this, but it bears mentioning - when they convert to SQL server, all tables must have a Primary Key declared or you won't be able to insert data (at least that's been my findings thus far when dealing with Access and/or VB and SQL server).
Select the maximum value of the id field (assuming it is sequential):

Select Max(YourIDField) From YourTable
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can use @@IDENTITY, if you're using Access 2000+. Otherwise you're out of luck:


this would return the last inserted autonumber ... there is no real concept of Scope in Access, since the tables don't support triggers and such ... so you'd need to issue this as soon as needed.

If you're not using ADO/DAO, how are you issuing these SQL commands? At some point, you must connect before you can query the database ...
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Richard QuadlingSenior Software DeveloperAuthor Commented:
I'm using ODBC via PHP. I'm only using an Access DB as that is what the client has. The overall app is being converted to SQL server, but in the meantime, rather than me trying to trudge my way through the VB code running the app, I've got a web app. A lot easier to use for the client.

Am I right in thinking that the table has to have an autoinc column? For some of the tables this is NOT the case. Rows are not unique. (Don't tell me! I didn't write it!)
If u want to use SCOPE_IDENTITY, dont your fields in Access have to be setup that way?


Richard QuadlingSenior Software DeveloperAuthor Commented:
Yes. I use a fair amount of normalisation for my dbs. This access project I've been given is only 40 tables, but next to no normalisation. Duplicate data everywhere and very few tables have uniqueids.

I'm not bothering to convert the DB, but actually start from scratch and do a data take on exercise which will clean and copy the data.

Nothing I'm not familiar with.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.