Microsoft KB Archive/97003

{|
 * width="100%"|

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

 * }

Q97003

-

The information in this article applies to:


 * 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
Microsoft has confirmed this to be a problem in Microsoft Access versions 1.0 and 1.1. This problem no longer occurs in Microsoft Access version 2.0.

Keywords : kbusage

Issue type : kbbug

Technology :