How to Increase Microsoft Access Max Locks

Microsoft Access allows you to create scalable databases that are capable of storing large amounts of data. However, when running queries or operations that access a table with a large number of records, or rows, you might receive insufficient disk space or memory errors. Access is capable of storing millions of records in a single table -- up to 1 GB in a single table and 2 GB in the database -- and many more if you connect several databases in an application. Memory errors in Access usually occur because of a "File Lock" setting that is set too low in the Windows Registry. Nevertheless, there are several permanent and temporary solutions to the problem.

Click "Start," then type "regedit" (without quotes) in the search box. Press the "Enter" key.

Click the "Computer" link in the left windowpane. Navigate to the "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0" value entry" if running Access versions 97, 2000, 2002 or 2003. If Running Access 2007 or 2010, navigate to the "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\ACE" entry.

Double-click the "MaxLocksPerFile" entry on the right side of the window. Change the number in the "Value Data" field to a higher value, and then click "OK." By default, Windows uses a "MaxFileLocksPerFile" value of 9500. Change the value sufficient to handle the maximum number of rows in the largest data table. Leave room for growth in the database as well. For example, if the largest table in your database has 100,000 records, set the value to at least 200,000 to accommodate new data entry.

Close the Registry Editor.

Launch Microsoft Access and open the database causing the Max Lock errors.

Click "Database Tools" on the menu or ribbon bar, then the "Visual Basic" button. The Visual Basic for Applications window opens.

Right-click the name of the database in the "Project" pane. Click "Insert," then "Module" on the pop-up menu.

Enter the following code under the "Option Compare Database" label:

DAO.DBEngine.SetOption dbmaxlocksperfile,200000

Change "200000" to the desired number of Max Locks, and then save your work. Access increases the number of max file locks to the value you entered. Once you exit your application, the setting reverts to the original value in the registry.

Cite this Article A tool to create a citation to reference this article Cite this Article

About the Author

Jeff Grundy has been writing computer-related articles and tutorials since 1995. Since that time, Grundy has written many guides to using various applications that are published on numerous how-to and tutorial sites. Born and raised in South Georgia, Grundy holds a Master of Science degree in mathematics from the Georgia Institute of Technology.