Solved

What is the equivalent to SQL Servers SCOPE_IDENTITY()?

Posted on 2006-07-19
6
798 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 85
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 85

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

726 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