Who gets the priority to Access when 2 users try to connect to?

If 2 users connect to Access and run 开发者_高级运维query that check the last number, increase this number and insert a record into the database.

Who gets the priority?

Why do I see two duplicate records with the same number?

This is a concurrency problem:

Both user A and B read the current value from the database. Then A increments it on the client side and writes it back to the database but B already has read the value and doesn't know about the incremented value. Like A before, B increases the value and writes it back too, overwriting A's change with the same number.

This kind of unique number generation is not safe. You should let the database do the incrementation by using an autoincrement value.

Alternatively you could have the following construct:

• Create an extra ID table.
• Create a stored procedure in which you
  • Lock access to the ID table
  • Read the current value
  • Increment it and write it back
  • Unlock access
  • Return the incremented value

Creating an ID table would prevent locking your data table.

Neither of them got "priority". That is no longer a useful term when you talk about non-atomic operations. An example will illustrate the issue here.

Here's what your script looks like:

• Read value from database.
• Increment by 1.
• Write new value to database.

This works when only one person uses the database at a time. When more than one person does, things won't work. Here's what happens now:

• Original value in DB is "100".
• Client A reads "100".
• Client A increments "100" locally to "101", but hasn't yet written it.
• Client B reads "100".
• Client B increments "100" locally to "101", but hasn't yet written it.
• Client A writes "101".
• Client B writes "101".

This is called a race condition, and it happens because you didn't use a transaction. With a transaction, here's what might happen instead:

• Original value in DB is "100".
• Client A begins a transaction on this value.
• Client A reads "100".
• Client A increments "100" locally to "101", but hasn't yet written it.
• Client B tries to begin a transaction, but there is already a pending transaction, so it cannot start yet.
• Client A writes "101".
• Client A closes the transaction and the value is actually written.
• Client B begins a transaction on this value.
• Client B reads "101".
• Client B increments "101" locally to "102", but hasn't yet written it.
• Client B writes "102".
• Client B closes the transaction and the value is actually written.

Well, going from the last question in your question, the reason you see two records with duplicate numbers is because you allow that in your schema/database/table design to begin with. Start by adding constraints and relations in your database and your scenario would throw an error instead and you could use optimistic concurrency to start with from the client side, and/or continue on with unique key generation on the database side and/or use transactions and so on.

This is an Access frequently-asked question. If you Google on "Access multi-user custom counter" you get a whole host of solutions.

They are usually based on using a table to hold the "seed" value, which is edited exclusively so that only one user at a time could change that seed value. There may be other approaches using Jet/ACE transactions, but this method is much simpler.

Note that the two of the three solutions from MS use ADO, which is not advisable in Access, but probably appropriate from outside Access.