While performing on operations on table, error there isn't enough disk space or memory if operation creates a large number of page locks. Error will come if it exceeds the MaxLocksPerFile value. By default MaxLocksPerFile value is 9500. If any operation, creates page locks greater then 9500, then this error will come.
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 to undo the data changes this action query is about to make. The cause of error is same, exceed from MaxLocksPerFile value. To solve this, firstly we have to generate this error, for this we need a database records greater than 9500. When we have to try performing delete all operation, the error will generated as shown in fig 1.1.
Cause: 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.
Resolution: We have two ways to solve this error. One is temporary and second is permanent.
Temporary solution: We can write in VBA code editor-
Permanent solution : We can change the value of key in registry of widows first open Registry Edit form RUN window by type regedit command and then go to this folder -HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0\MaxLocksPerFile and change the value of MaxLocksPerFile key as many as you want to increase. When we open the registry, the default value is 9500 as shown in Fig 1.2.
We can change the default value of max locks per file as shown in fig 1.3. After changing this value we can perform operations on large tables.
VBA code to reset the value:
Option Compare Database Function CreateBigTable() Dim db As Database, rs As Recordset Dim iCounter As Integer, strChar As String Set db = CurrentDb db.Execute "CREATE TABLE BigTable (ID LONG, Field1 TEXT(255),Field2 TEXT(255), Field3 TEXT(255), Field4 TEXT(255))", dbFailOnError Set rs = db.OpenRecordset("BigTable", dbOpenDynaset) DBEngine.SetOption dbMaxLocksPerFile, 200000 ' set max locks per file iCounter = 0 strChar = String(255, " ") While iCounter <= 10000 rs.AddNew rs!ID = iCounter rs!Field1 = strChar rs!Field2 = strChar rs!Field3 = strChar rs!Field4 = strChar rs.Update iCounter = iCounter + 1 Wend MsgBox "Done!" End Function
It is advised that the information provided in the article should not be used for any kind formal or production programming purposes as content of the article may not be complete or well tested. ERP Makers will not be responsible for any kind of damage (monetary, time, personal or any other type) which may take place because of the usage of the content in the article.