MS Access true IDENTITY column

State of play:

Databases ensure data integrity and relationship through both primary and foreign keys, and MS Access is no exception.

Well, that's not completely true ...

• A primary key should not be null.  - No problem, the NOT NULL constraint enforces that behavior
• A primary key should be unique. No problem again, the UNIQUE constraint enforces that behavior

A primary key should be used only once, and this is where MS Access fails as the following sample will demonstrate:

1. Create a table, add an ID column, set its data type to AutoNumber
2. Now add a second column, the name and data type doesn't matter
3. Add some data to the table (10 rows)
4. Delete some rows in the middle and then delete the last row
5. According to the 3rd primary key rule, deleted IDs should be lost, forever
6. Now close your table, and compact the database.
7. Open your table again and add a new row.

Surprise !! The Newly generated ID isn't 11 as we should expect, but 10 again (remember you deleted it?)

Why may you ask?

Microsoft Access internally keeps track of the last ID used, but this ID is reset to the max ID + 1 when you compact the database. This effectively breaks the 3rd primary key rule.

What can we do about this?

Data macros to the rescue

Since MS Access 2010, Microsoft introduced the Data Macro, which can perform various actions before inserting, deleting, updating data and after inserting and after updating data.

These macros (also known as Triggers in other database engines) are executed automatically whenever you add, insert or update data, manually, trough forms, or programatically. They are mostly used to validate data (i.e: ensure data integrity), but we can use them to generate a true ID number that will never - ever - be re-used, even if the database is compacted.

To achieve this, we first need a table that will keep track of the max ID is used. Also, we will need data macros that will pull the max ID before we add a new row, increase the max ID after we save a new row, and prevent any ID update.

So, in this example, create a table, name it IDtrackers

1. Add a column, name it TableName, data type Short Text, don't allow empty strings, set it as a primary key.
2. Add a column, name it maxID, data type Numeric long, don't allow null values.
3. Add 1 row, values are "myTable" for the tableName column, and 0 for the maxID column.
4. Create a second table named MyTable
5. Add a column, name it ID, data type Numeric long, no null value, set it as a primary key.
6. Add a column, name it data, data type doesn't matter.
7. In the ribbon, on the Creation tab, choose the Create data macro button, and choose the 'Before update' event.
8. Copy and paste the following code (hopefully, it becomes self-explanatory in the editor):

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
  <DataMacro Event="BeforeChange">
    <Statements>
      <ConditionalBlock>
        <If>
          <Condition>[IsInsert]</Condition>
          <Statements>
            <Comment>New record, pull the primary key value from IDtrackers table</Comment>
            <LookUpRecord>
              <Data>
                <Reference>IDtrackers</Reference>
                <WhereCondition>[TableName]="MyTable"</WhereCondition>
              </Data>
              <Statements>
                <Action Name="SetField">
                  <Argument Name="Field">myTable.ID</Argument>
                  <Argument Name="Value">[IDtrackers].[maxID]</Argument>
                </Action>
              </Statements>
            </LookUpRecord>
          </Statements>
        </If>
        <Else>
          <Statements>
            <Comment>Updated record, don't allow the primary key to be updated.</Comment>
            <ConditionalBlock>
              <If>
                <Condition>Updated("ID")</Condition>
                <Statements>
                  <Action Name="RaiseError">
                    <Argument Name="Number">5001</Argument>
                    <Argument Name="Description">Primary key can't be updated</Argument>
                  </Action>
                </Statements>
              </If>
            </ConditionalBlock>
          </Statements>
        </Else>
      </ConditionalBlock>
    </Statements>
  </DataMacro>
</DataMacros>

    9.    Close and save.

This data macro will automatically fill the ID column when we insert a new record. It will also raise an error if we attempt to modify an existing ID.

In the ribbon, on the Creation tab, choose the 'Create data' macro button, and choose the 'After insert' event.
Copy and paste the following code:

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
  <DataMacro Event="AfterInsert">
    <Statements>
      <Comment>Find the primary key value and update it</Comment>
        <LookUpRecord>
          <Data Alias="Found">
            <Reference>IDtrackers</Reference>
            <WhereCondition>[IDtrackers].[TableName]="MyTable"</WhereCondition>
          </Data>
          <Statements>
            <EditRecord>
            <Data/>
            <Statements>
              <Action Name="SetField">
                <Argument Name="Field">Found.maxID</Argument>
                <Argument Name="Value">[Found].[maxID]+1</Argument>
              </Action>
            </Statements>
          </EditRecord>
        </Statements>
      </LookUpRecord>
    </Statements>
  </DataMacro>
</DataMacros>

Now "Close and save"

This data macro will update the maxID in the IDtrackers table whenever a record is added

Finally, hide your table (this will prevent novices from editing it)

Now you can compact the database and IDs that are thrown away will never show up again

Final note, for perfectionists:

You probably noticed that MS Access auto-numbers start at 1, effectively cutting by 2 the amount of numbers available (guess it is an arbitrary decision from Microsoft, don't ask why)

You can set the starting value in the IDtracker table at the minimum allowed by long data type, in other words:
-2147483648 (-2^31 or 0x80000000)

Hope you found this useful!