Scope_Identity() in Microsoft Access

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(开发者_StackOverflow社区FIELDLIST) VALUES(VALUELIST)
SELECT * FROM TABLE WHERE PrimaryKeyID = SCOPE_IDENTITY()

---

Try:

SELECT @@Identity

---

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.

---

Hmmm... there's a lot here that raises flags.

• All Access tables should have an AutoNumber field as the primary key field
• Data entry into tables should be done via a bound form
• Access is not intended to have a WAN in between the front end and back end files, and may not function correctly.
• SQL Server should be used if the file size is expected to be near or beyond 2G, a large quantity of simultaneous users, a large quantity of Long Text fields, or if there are other requirements for a back end database - such as providing data to a web server. Access is a front end database and thus the tables are nearly entirely passive.