ACC1x: Cannot Add Counter Columns to Tables with > 4 MB Data

• Microsoft Access versions 1.0, 1.1

SYMPTOMS

You cannot add Counter data type columns to existing tables containing roughly greater than 4 megabytes (MB) of data.

CAUSE

This problem occurs because of a limitation in the Microsoft Access transaction model, which is limited to a transaction size of about 4 MB.

The problem occurs with Counter columns because the table data for columns is updated when the new column is added. The update takes place in a transaction, and with large tables (greater than 4 MB), the update encounters the Microsoft Access transaction size limit. When this happens, the transaction rolls back and the Counter column is not successfully added.

RESOLUTION

Use the following steps to add a Counter column to a large table:

1. Copy and paste the table's structure (not the table's data) to a new table.

2. Add a Counter data type column to the new table.

3. Create an append query that transfers the data from the old table to the new table.

4. Verify that the new table has the correct data.

5. Delete the old table.

6. Rename the new table to the name of the old table.

STATUS