Avoiding autoincrement collisions with multiple OnWeb sessions

Updated 4 days ago | Filed under OnWeb | PDF

Problem

If two users launch OnWeb at the same time, and append a row to a table with an AutoNumber which has the 'New Values' property set to increment, both users could end up with the same AutoNumber, causing data to be overwritten.

Solution

There are two possible solutions to this problem.

Solution 1: Use random AutoNumber fields

Steps:

  1. Open the affected table in Access's design view.

  2. Click on the name of the AutoNumber field.

  3. In the properties section at the bottom of the screen, change the New Values property to Random instead of Incremental.

  4. Save the table.

Random numbers are generated in the range of -231 to 231 - 1, for an effective range of 232. That means that the chance of multiple users opening OnWeb sessions at the same time and receiving the same ID is effectively more than 99.99% improbable. See the birthday problem for a good description of how this probability is calculated.

This solution is recommended for databases which have been pre-designed, and are being modified for OnWeb usage.

Solution 2: Use Replication ID fields

Steps:

  1. Open the affected table in Access's design view.

  2. Click on the name of the AutoNumber field.

  3. In the properties section at the bottom of the screen, change the Field Size property to Replication ID.

  4. Save the table.

Replication IDs are globally unique, and thus, users who employ these to create rows will never suffer data collisions. Regrettably, Access' support for printing and manipulating these IDs is limited, so these should be used only your joins between tables are well-tested. Also, Replication IDs are very long and inconvenient for users to read, so you should avoid displaying them in your program's user interface.

This solution is recommended for users familiar with Replication IDs, and who are creating new databases where table relationships are yet to be defined.

Related Articles

Unrelated Articles