When an Access database is replicated new replication features are added to the database, during conversion MUST will automatically remove the replication features to ensure that your database is correctly converted.

When a database is replicated, autonumber fields are set to Random. We would recommend that you replace these fields with a standard Increment autonumber field, although in a complex system this is not always possible, so MUST has special support to assist you here.

Random Autonumber Fields

When a database is replicated, autonumber fields are set to Random. We would recommend that you replace these fields with a standard Increment autonumber field, although in a complex system this is not always possible, so MUST has special support to assist you here.

If you were to look inside Access at each of these random autonumber fields you would see that each field uses a special inbuilt function in Access to provide the Random Values called GenUniqueID().

?currentdb.TableDefs("categories").Fields("CategoryID").DefaultValue
GenUniqueID()

When MUST upsizes your database it will create an equivalent function in SQL Server to support this operation.

Replication Fields and Indexes

Access will also have added several special fields and indexes to each table to support replication. MUST will remove these fields and indexes for you.

A example of these fields and corresponding indexes are shown below.

Conversion Process

Below you can see a notification of a detected replicated autonumber, and the subsequent reference that will be made to a new SQL Server function to support the random autonumber generation.

Below you can see the correctly upsized table, with the new supporting function.

To implement the random number generation MUST creates a view as shown below.

The MUST generates a function which can return the new random number.

The reason that the rand() function is not written inside the function is because SQL Server will not allow this, as rand() is a non-deterministic function. The method we use here is a well known standard work-around technique using views to get a non-deterministic function into a user defined function.

Once the database is converted you could substitute your own random number generating function if required.