I am trying to execute a data definition Query that will change the data type in one field of a table and I am getting the "File sharing lock count exceeded. Increase MaxLocksPerFile registry entry" error message. I don't really want to permanantly change the registry cause I don't feel comfortable doing that so I tried the following method:
Method 2: Use the SetOption method to temporarily change MaxLocksPerFile
Note The sample code in this article uses Microsoft Data Access Objects. For this code to run correctly, 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.
The SetOption method temporarily overrides the default number of locks per file. You set the default number of locks per file when you set the MaxLocksPerFile registry key. You set the new value by using the SetOption method. The new value is valid until you close the DBEngine object. To use Method 2, follow these steps:
1. Open Microsoft Access.
2. Open a database, and then press Alt+F11 to launch the Visual Basic editor.
3. On the Microsoft Visual Basic –[ (Code)] window, click Immediate Window in the View menu.
4. In Immediate Window, enter the following code.
DAO.DBEngine.SetOption dbmaxlocksperfile,15000
5. Press the ENTER key to run the line of code.
Note This temporarily sets the MaxLocksPerFile value to 15,000.
To process large transactions, set the MaxLocksPerFile value to meet your requirement, and then run the transactions in the session.
Changes you make to the MaxLocksPerFile setting by using the SetOption method are available only for the current session.
Next I ran this code:
This method didn't work I still get the same error message
Method 2: Use the SetOption method to temporarily change MaxLocksPerFile
Note The sample code in this article uses Microsoft Data Access Objects. For this code to run correctly, 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.
The SetOption method temporarily overrides the default number of locks per file. You set the default number of locks per file when you set the MaxLocksPerFile registry key. You set the new value by using the SetOption method. The new value is valid until you close the DBEngine object. To use Method 2, follow these steps:
1. Open Microsoft Access.
2. Open a database, and then press Alt+F11 to launch the Visual Basic editor.
3. On the Microsoft Visual Basic –[ (Code)] window, click Immediate Window in the View menu.
4. In Immediate Window, enter the following code.
DAO.DBEngine.SetOption dbmaxlocksperfile,15000
5. Press the ENTER key to run the line of code.
Note This temporarily sets the MaxLocksPerFile value to 15,000.
To process large transactions, set the MaxLocksPerFile value to meet your requirement, and then run the transactions in the session.
Changes you make to the MaxLocksPerFile setting by using the SetOption method are available only for the current session.
Next I ran this code:
Code:
Sub MaxLocksPerFile()
Dim sqlCode As String
sqlCode = "ALTER TABLE [Bad Debt Time Stamp3]ALTER COLUMN INVOICE TEXT(12)"
DAO.DBEngine.SetOption dbMaxLocksPerFile, 500000
DoCmd.RunSQL sqlCode
End SubThis method didn't work I still get the same error message
