ACC97: Error: "There Isn't Enough Disk Space or Memory"

• Microsoft Access 97

SYMPTOMS

IMPORTANT: This article contains information about editing the registry. Before you edit the registry, you should first make a backup copy of the registry files (System.dat and User.dat). Both are hidden files in the Windows folder.

Advanced: Requires expert coding, interoperability, and multiuser skills.

When you perform an operation on a table, you may receive the following error message if the operation creates a large number of page locks:

   There isn't enough disk space or memory.

   There isn't enough disk space or memory to undo the data changes this
   action query is about to make.

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

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: Using Registry Editor incorrectly can cause serious problems that may require you to reinstall Windows 95. Microsoft cannot guarantee that problems resulting from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk.

For information about how to edit the registry, view the Changing Keys And Values online Help topic in Registry Editor (Regedit.exe). Note that you should make a backup copy of the registry files (System.dat and User.dat) before you edit the registry.

Use Registry Editor to increase the MaxLocksPerFile value under the following key:

   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Jet 3.5

Method 2: Using SetOption to Change MaxLocksPerFile Temporarily

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 DAO
     (Data Access Objects).  Queries run through the Microsoft Access user
      interface, will still use the settings within the registry.

   Sub LargeUpdate()
      On Error GoTo LargeUpdate_Error
      Dim db As Database, ws As Workspace

      ' Set MaxLocksPerFile.
      DBEngine.SetOption dbMaxLocksPerFile, 200000

      Set db = CurrentDb
      Set ws = Workspaces(0)

      ' Perform the update.
      ws.BeginTrans
      db.Execute "UPDATE LargeTable 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"
      Exit Sub
   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:

1. Open the query in Design view.

2. On the View menu, click Properties.

3. Click an empty space in the upper half of the query window to display the Query Properties dialog box.

4. Set the UseTransaction property to No.

5. Save the query and close it.

This behavior is by design.

MORE INFORMATION

The MaxLocksPerFile setting in the Windows Registry prevents transactions in the Microsoft Jet database engine from exceeding a specified value. If a transaction attempts to create locks in excess of the MaxLocksPerFile value, then the transaction is split into two or more parts and partially committed. This feature was added to Microsoft Access 97 to prevent Netware 3.1 server crashes when the specified Netware lock limit was exceeded, and to improve performance with both Netware and Windows NT.

Steps to Reproduce Behavior

The following example uses a Visual Basic procedure to create a table with 10,000 records in it, and then modifies the table in order to cause the error message:

1. Open the sample database Northwind.mdb.

2. Create a module and type the following procedure.

   NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code.

         Sub 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)
            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 Sub
   
   

3. To run the procedure, type the following line in the Debug window, and then press ENTER.

          CreateBigTable
   

   The procedure creates a table called BigTable with 10,000 records in it.

4. Save the module as Module1 and close it.

5. Open the BigTable table in Design view.

6. Change the FieldSize property of Field4 to 253.

7. Save the table. Click OK when you are prompted that some data may be lost. Note that after a time you receive the following error messages:

         Microsoft Access can't change the data type.
         There isn't enough disk space or memory.
   

         Errors were encountered during the save operation. Data types were
         not changed. Properties were not updated.
   

REFERENCES

For more information about the SetOption method, search the Help Index for "SetOption method."

For more information about customizing Windows Registry settings for Microsoft Access 97, search the Help Index for "Registry."

For more information about the UseTransaction property, search the Help Index for "UseTransaction property."