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()

Omer

Answer 1

Try:

SELECT @@Identity

Horoscopy

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.

Bushman

This is a good piece of advice. A lot of hosting companies provide 1 SQL Server database plus as many Microsoft Access databases as you need. I'm trying to limit the amount of tables I'm piling into my SQL Server database. I'll have to check to see if there are any hosting companies that provide SQL Server Express for free. – Omer

Howe many tables are we talking about? Is the number of tables growing continuously? If not, your single SQL Server instance should be able to handle anything you throw at it. If the tables are growing continuously (are you adding a new table every day?), and the tables all have the same fields, consider adding another field to distinguish the table instances (a date field, perhaps?) and put all of the data into a single table. – Bushman

Answer 3

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.

Validate