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: 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:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0
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 Temporarily
NOTE: 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:
Sub LargeUpdate()
On Error GoTo LargeUpdate_Error
Dim db As DAO.Database, ws As DAO.Workspace
' Set MaxLocksPerFile.
DBEngine.SetOption dbMaxLocksPerFile, 200000
Set db = CurrentDb
Set ws = Workspaces(0)
' Perform the update.
ws.BeginTrans
db.Execute "UPDATE BigTable SET Field1 = 'Updated Field'", _
dbFailOnError
ws.CommitTrans
db.Close
MsgBox "Done!"
Exit Sub
LargeUpdate_Error:
MsgBox Err & " " & Error
ws.Rollback
MsgBox "Operation Failed - Update Canceled"
End Sub
Method 3: Setting the UseTransaction Property in an Action Query
If 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:
- Open the query in Design view.
- On the View menu, click Properties.
- Click an empty space in the upper half of the query window to display the Query Properties dialog box.
- Set the UseTransaction property to No.
- Save the query and close it.