Article ID: 209940 - View products that this article applies to.
This article was previously published under Q209940
IMPORTANT: This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:
(https://support.microsoft.com/kb/256986/EN-US/ )Description of the Microsoft Windows Registry
Advanced: Requires expert coding, interoperability, and multiuser skills.
This article applies only to a Microsoft Access database (.mdb).
For a Microsoft Access 2002 and Access 2003 version of this article, see 286153
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:
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.
There isn't enough disk space or memory to undo the data changes this action query is about to make.
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.
There are several ways to work around this behavior:
Method 1: Changing MaxLocksPerFile in the Windows RegistryWARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.
Use Registry Editor to increase the MaxLocksPerFile value under the following key:
To do so, right-click the MaxLocksPerFile value, click Modify, click the Decimal option, increase the Value Data from 9500 to some higher value, and then click OK.
Note that this method changes the Windows Registry setting for all applications that use the Microsoft Jet database engine version 4.0.
Method 2: Using SetOption to Change MaxLocksPerFile TemporarilyNOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. The SetOption method temporarily overrides values for the Microsoft Jet database engine keys in the Windows Registry. The new value remains in effect until you change it again, or until the DBEngine object is closed.
NOTE: Changes made to the MaxLocksPerFile setting using the SetOption method will only be available through the current session of Data Access Objects (DAO). Queries run through the Microsoft Access user interface, will still use the settings within the registry.
The following code sample sets MaxLocksPerFile to 200,000 before executing an update operation inside a transaction:
Method 3: Setting the UseTransaction Property in an Action QueryIf a stored action query causes the error, you can set its UseTransaction property to No. Note that if you do this, you will not able to roll back your changes if there is a problem or an error while the query is executing:
The MaxLocksPerFile setting in the Windows Registry prevents transactions in the Microsoft Jet database engine from exceeding a specified value. If a transaction tries to create locks in excess of the MaxLocksPerFile value, the transaction is split into two or more parts and partially committed. This feature was added to Microsoft Access 97 to prevent Netware 3.1 server crashes when the specified Netware lock limit was exceeded, and to improve performance with both Netware and Microsoft Windows NT.
Steps to Reproduce BehaviorThe following example uses a Visual Basic procedure to create a table with 10,000 records in it, and then modifies the table in order to cause the error message:
For more information about the SetOption method, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type SetOption method in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
For more information about customizing registry settings for the Jet database engine, click Microsoft Access Help on the Help menu, type Customizing Windows Registry settings for Microsoft Jet in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
For more information about the UseTransaction property, click Microsoft Access Help on the Help menu, type UseTransaction property in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Article ID: 209940 - Last Review: June 23, 2005 - Revision: 3.0