Microsoft Can't Change the Data Type, Not Enough Memory or Hard Drive Space

Saturday, March 16, 2002 23:28:54

Derived from Knowledge Base Article Q209940

home

Applies mainly to version 2000 of Access. For a Microsoft Access 2002 version of this article, see Q286153 .

SYMPTOMS
When you perform an operation on a table, you may receive the following error message if the operation creates a large number of page locks:

There isn't enough disk space or memory.

If you run an action query on a large table, you may receive the following error message:

There isn't enough disk space or memory to undo the data changes this action query is about to make.

CAUSE
The page locks required for the transaction exceed the MaxLocksPerFile value, which defaults to 9500 locks. The MaxLocksPerFile setting is stored in the Windows Registry.

RESOLUTION
There are several ways to work around this behavior:

You can use Regedit.exe to edit the Windows Registry and change the MaxLocksPerFile value permanently.

You can use the SetOption method of the DBEngine object to change the MaxLocksPerFile value temporarily in code.

If the error occurs when you run an action query, you can modify the query and set its UseTransaction property to No .

Method 1: Changing MaxLocksPerFile in the Windows Registry WARNING : Using Registry Editor incorrectly can cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that problems resulting from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk.

For information about how to edit the registry, view the "Changing Keys and Values" Help topic in Registry Editor (Regedit.exe) or the "Add and Delete Information in the Registry" and "Edit Registry Data" Help topics in Regedt32.exe. Note that you should back up the registry before you edit it. If you are running Windows NT or Windows 2000, you should also update your Emergency Repair Disk (ERD).

Use Registry Editor to increase the MaxLocksPerFile value under the following key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0

Note that this method changes the Windows Registry setting for all applications that use the Microsoft Jet database engine version 4.0