How to Increase Microsoft Access Max Locks

Written by jeff grundy Google
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Increase Microsoft Access Max Locks
Access uses "File Locks" to prevent file changes during queries or searches. (Hemera Technologies/AbleStock.com/Getty Images)

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.

Skill level:
Moderate

Other People Are Reading

Instructions

    Increase MaxLocks Permanently in Registry

  1. 1

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

  2. 2

    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.

  3. 3

    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.

  4. 4

    Close the Registry Editor.

    Change Max Locks Temporarily in Code

  1. 1

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

  2. 2

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

  3. 3

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

  4. 4

    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.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.