Solved

What is the equivalent to SQL Servers SCOPE_IDENTITY()?

Posted on 2006-07-19
6
791 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:Richard Quadling
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:Richard Quadling
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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:Richard Quadling
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

840 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