Scope_Identity() in Microsoft Access

Question

I'm writing a database in Microsoft Access to save the client from having to buy SQL Server. How do you get the primary key of the row that I just inserted? In SQL Server, it's

INSERT INTO TABLE(FIELDLIST) VALUES(VALUELIST)
SELECT * FROM TABLE WHERE PrimaryKeyID = SCOPE_IDENTITY()

Answer 1

Try:

SELECT @@Identity

Answer 2

If it's about money, get SQL Server Express; it's free.

If you are writing an application, you still need Microsoft Access, but you can attach a SQL Server Express database to your application, and use the SQL Server Express database as your storage medium.

If the application is to be multi-user, using SQL Server Express as your backend database from the outset will make the application more reliable, especially if you lack the specialized knowledge needed to create a multi-user application properly using Access as a backend.

Multi-user applications have two parts: the database and the application frontend. The database will reside on one machine, and will be shared by each application frontend. The application frontend will be copied to each user's computer.

If you are serious about Access development, get this book. Although it is dated, it is still the definitive work on Access development.