Solved

What is the equivalent to SQL Servers SCOPE_IDENTITY()?

Posted on 2006-07-19
6
789 Views
Last Modified: 2008-01-09
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.
0
Comment
Question by:RQuadling
6 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 17137282
Select the maximum value of the id field (assuming it is sequential):

Select Max(YourIDField) From YourTable
0
 
LVL 84
ID: 17137300
You can use @@IDENTITY, if you're using Access 2000+. Otherwise you're out of luck:

SELECT @@IDENTITY As LastID

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 ...
0
 
LVL 40

Author Comment

by:RQuadling
ID: 17137330
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!)
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 65

Expert Comment

by:rockiroads
ID: 17137576
If u want to use SCOPE_IDENTITY, dont your fields in Access have to be setup that way?

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp

0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 17137578
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).
0
 
LVL 40

Author Comment

by:RQuadling
ID: 17137732
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.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now