CODEWITHSUNDEEP
ms-access
Matt Hall
Matt Hall

Reputation: 2412

Access Autonumber and Maintaining Uniqueness in Multi-user Database

Somewhat general question about Access, but hopefully has a fairly easy answer:

When you use autonumber in a shared multi-user database, is Access doing anything in particular to ensure that the number it assigns is unique what with other users adding records at the same time?

Does it immediately claim the next number for the first user who has connected to that table, or does it wait until the new record is about to be saved and check the highest number of all saved records in the table just before assigning the next number?

Is its uniqueness robust in a multi-user environment?

Upvotes: 2

Views: 2347

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123829

In order to fully answer your question it is important to make the distinction between

  • "Access" (the application), and

  • the "Access Database Engine" (a.k.a. "ACE") and its predecessor, the Jet database engine.

When you use autonumber in a shared multi-user database, is Access doing anything in particular to ensure that the number it assigns is unique what with other users adding records at the same time?

[...]

Is its uniqueness robust in a multi-user environment?

Yes. ACE, like many other database engines, ensures that Identity columns (what Access calls "AutoNumber fields") are unique in a multi-user environment by normally assigning that number at the time the record is committed (written) to the table. However, ACE does offer Access the opportunity to get its AutoNumber value in advance (see below).

Does [Access] immediately claim the next number for the first user who has connected to that table,

No. Simply "connecting to" a table (e.g., by doing a SELECT, or opening a Recordset) does not affect the counter of an AutoNumber field.

or does [Access] wait until the new record is about to be saved and check the highest number of all saved records in the table just before assigning the next number?

That depends...

If the "table" is a linked table to an ODBC data source (e.g., a SQL Server table with an IDENTITY column) then yes, Access "waits" until the user does something that will commit (save) the new record, at which point it submits the new record to the database server and then retrieves the AutoNumber value for that record (e.g., via SELECT @@IDENTITY in SQL Server, or a similar mechanism for other database engines).

However, if the "table" is a native ACE/Jet table then you may notice that the new AutoNumber value appears immediately after you start typing into a new record (e.g., in Datasheet View, or in a bound form). In this case Access (the application) tells ACE (the database engine) that it may want to insert a new record and requests the AutoNumber value right away. ACE returns the value and increments the counter so another user making the same request will get the next number in the sequence. Note that this process "consumes" the AutoNumber value: it will either be used (if the user saves the record) or discarded (if the user decides not to save the record) but it won't be re-used. That's why

  • ACE AutoNumber fields that are "incrementing" (as opposed to "random") sometimes have "gaps" in them, and

  • if you start entering data, hit Esc to cancel the insert, and then start typing again, the AutoNumber value is different (because you "consumed" the previous AutoNumber value even though you did not save the record).

Upvotes: 4

Alexander Bell
Alexander Bell

Reputation: 7918

The answer is: Yes, it is. MS Access Database, as any other general RDB, is well suited to operate in multi-user environment (though performance-wise it might be not that fast as, for e.g. SQL Server, and it's data security model is not that sophisticated). Also, the uniqueness of the primary key (i.e. autonumber) is provided on the table level; in other words, it's not a GUID (globally unique ID).

Upvotes: 2

Related Questions