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.
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.
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 ...
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 ...
ASKER
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!)
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?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
Select Max(YourIDField) From YourTable