Solved

What is the equivalent to SQL Servers SCOPE_IDENTITY()?

Posted on 2006-07-19
6
792 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 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
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 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

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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 …

730 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