ACC1x: Transaction Limit Can Cause "Out of Memory" Error

• Microsoft Access versions 1.0 and 1.1

SYMPTOMS

When you attempt to make a change to a database, you receive one of the following error messages, even if you have ample system resources and disk space:

   Insufficient Disk Space or Memory

   -or-

   You are out of disk space or have exceeded maximum transaction size

CAUSE

Microsoft Access has a 4 MB transaction limit. All table design changes are embedded within a transaction to ensure database integrity. As a result, you will get the "Out of memory" error if you attempt to update the contents of a table this large or larger.

RESOLUTION

If, for example, a table contains a field of type Double that you want to change to Integer there are two ways to do this:

Method 1

1. Select and copy the desired table to Clipboard.

2. From the Edit menu, choose Paste (paste only the table structure to a new table name).

3. Change the data type of the desired column in this new table.

4. From the Edit menu, choose Paste (paste append the data to this new table).

Method 2

1. Add a new column to the table with the data type you want.

2. Execute an update query, where the new column is assigned the value of the column you want to change.

3. Delete the old column from the table.

STATUS