MS-Access / Getting Started

---

Replicated Databases and Random Autonumbers

If your database was at one time replicated, then the tables will have picked up a number of special replication fields such as s_Generation, s_Lineage, s_GUID, or s_ColLineage. It is a good idea to remove these now unwanted fields prior to upsizing, and possibly convert any ReplicationID fields, if used as a primary key, to standard autonumbers.

A general feature of Access that is associated with replication is the option to have a random Autonumber sometimes used as a primary key. If, for example, in an Access database you have a table called Customer2 with a field NewId that was a random Autonumber, then if you look in CurrentDB.TableDefs("Customers2").Fields("NewId").DefaultValue, you can spot a function called GenUniqueID(); this is the internal function that Access uses to generate the random Autonumbers. One nice feature of the Upsizing Wizard is that it will automatically write the following trigger code (usp_GenUniqueID.sql) to generate the random Autonumber in SQL Server, and sets a default of 0 for the field NewId, which is the primary key:

CREATE TRIGGER [dbo].[T_Customers2_ITrig] ON [dbo].[Customers2]
FOR INSERT AS
    SET NOCOUNT ON
    DECLARE @randc int, @newc int /* FOR AUTONUMBER-EMULATION CODE */
    /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'NewId' */
    SELECT @randc = (SELECT convert(int, rand() * power(2, 30)))
    SELECT @newc = (SELECT NewId FROM inserted)
    UPDATE Customers2 SET NewId = @randc WHERE NewId = @newc
GO

The preceding approach, however, has a logical flaw: if an insert is from data in another table with multiple rows or using multiple values, then, as demonstrated in the code that follows, it generates duplicate primary key values of 0 and fails (remember the trigger fires after the insert). The good news is that this is very easily fixed, and the trigger code gives you all the information to write a SQL Server function to resolve this. This code is interesting because you want to create a generic function that uses the RAND function, but it turns out that you are not allowed to use the RAND system function inside a user-defined function. So, you must employ a cunning trick and wrap the RAND function inside a View, and then you are allowed to do this.

After allowing the Upsizing Wizard to convert the sample table Customer2, you can cause the previously described trigger to fail by using the following code:

INSERT INTO Customers2(CompanyName)
VALUES('Test'), ('Test2')
GO

Next, to resolve this problem, you create a View and then a function to use the View. In the following, notice that the first definition of the function will not work; however, the code starting from creating the View will work:

-- This will not work
CREATE FUNCTION usp_GenUniqueID()
RETURNS int
AS
BEGIN
    DECLARE @randc INT
    SELECT @randc = (SELECT convert(int, rand() * power(2, 30)))
    RETURN (@randc)
END
GO
-- Create the view
CREATE VIEW vw_Rand
AS
    SELECT convert(int, rand() * power(2, 30)) As RandomSeed
GO
-- This will work
CREATE FUNCTION usp_GenUniqueID()
    RETURNS int
AS
BEGIN
    RETURN (SELECT * FROM vw_Rand)
END
GO

To complete this, you need to drop the existing trigger (by using DROP), drop the existing default, and then add your new default:

-- Drop the trigger
DROP Trigger T_Customers2_ITrig
GO
-- drop the default, your default name would be different to this example
ALTER TABLE [dbo].[Customers2]
DROP CONSTRAINT [DF__Customers__NewId__2C3393D0]
GO
ALTER TABLE [dbo].[Customers2] ADD

Allowing for changes to multiple rows in trigger code
The previous example demonstrates how trigger code should always be written to allow for multiple rows to be inserted, updated, or deleted; otherwise, you run the risk that at some point you perform an operation involving multiple rows and the application then generates errors. In some situations (although not in this case), if you cannot write the trigger for multiple rows, then you can consider using the following T-SQL to temporarily disable and then enable a trigger after completing the operation; this would only be recommended for use in maintenance and not while users are interacting with the system:

DISABLE TRIGGER T_Customers2_ITrig ON Customers2
GO
ENABLE Trigger T_Customers2_ITrig ON Customers2
GO

This example also shows the benefit in having constraints with easily managed names for triggers and other objects in the database.