Access Autonumber and Maintaining Uniqueness in Multi-user Database -

somewhat general question access, has easy answer:

when use autonumber in shared multi-user database, access doing in particular ensure number assigns unique other users adding records @ same time?

does claim next number first user has connected table, or wait until new record saved , check highest number of saved records in table before assigning next number?

is uniqueness robust in multi-user environment?

in order answer question important make distinction between

• "access" (the application), ,

• the "access database engine" (a.k.a. "ace") , predecessor, jet database engine.

when use autonumber in shared multi-user database, access doing in particular ensure number assigns unique other users adding records @ same time?

[...]

is uniqueness robust in multi-user environment?

yes. ace, many other database engines, ensures identity columns (what access calls "autonumber fields") unique in multi-user environment assigning number @ time record committed (written) table. however, ace offer access opportunity autonumber value in advance (see below).

does [access] claim next number first user has connected table,

no. "connecting to" table (e.g., doing select, or opening recordset) not affect counter of autonumber field.

or [access] wait until new record saved , check highest number of saved records in table before assigning next number?

that depends...

if "table" linked table odbc data source (e.g., sql server table identity column) yes, access "waits" until user commit (save) new record, @ point submits new record database server , retrieves autonumber value record (e.g., via select @@identity in sql server, or similar mechanism other database engines).

however, if "table" native ace/jet table may notice new autonumber value appears after start typing new record (e.g., in datasheet view, or in bound form). in case access (the application) tells ace (the database engine) may want insert new record , requests autonumber value right away. ace returns value , increments counter user making same request next number in sequence. note process "consumes" autonumber value: either used (if user saves record) or discarded (if user decides not save record) won't re-used. that's why

• ace autonumber fields "incrementing" (as opposed "random") have "gaps" in them, and

• if start entering data, hit esc cancel insert, , start typing again, autonumber value different (because "consumed" previous autonumber value though did not save record).