7 Ways To Fix Access There Isn’t Enough Memory To Perform This Operation Error

Summary:

This article explains how to fix Microsoft Access there is not enough memory to complete this operation error. Also get detailed information on when and why you are getting this not enough memory error in Access.

What Are The Circumstances Of Getting “There Isn’t Enough Memory“ Error in Access?

• While performing any operation on the Access table you may get the error message there isn’t enough disk space or memory. Mainly this happens when your performed operation makes a huge number of page locks.
• When you execute the action query on a big table you may get this not enough memory space error. After the occurrence of this error message all the data changes which the action query is going to do will revert back.

Why You Are Getting There Isn’t Enough Memory To Perform This Operation Error In Access?

Well, the very first reason behind getting this not enough memory error is that the page lock has exceeded the MaxLocksPerFile value which is important for transactions. By default, the value of MaxLocksPerFile is 9500 locks and it is saved in the Windows registry.

Other reasons can be insufficient RAM, lack of virtual memory, or failure of Windows update.

How To Fix Access There Is Not Enough Memory To Complete This Operation Error?

You can try the following workarounds to fix Access there is not enough memory to complete this operation error.

• Change MaxLocksPerFile value from the Regedit.exe
• Use the SetOption method to change the MaxLocksPerFile value Temporarily
•  In action query set the UseTransaction property
• Use Code For Adding Field In Access Design View
• Increase Paging File (Virtual Memory)
• Update Windows
• Add Extra RAM

Method 1: Change MaxLocksPerFile Value From The Regedit.exe

In your system registry Regedit.exe you need to change the value of MaxLocksPerFile permanently.

Use the Registry Editor for increasing the value of MaxLocksPerFile present within the following given key:

• For MS Access 2000/MS Access 2002/MS Office Access 2003 application running on the 32-bit Windows OS:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0

• For MS Access 2000/MS Access 2002/MS Office Access 2003 application running on the 64-bit Windows OS:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Jet 4.0

• For MS Access 2007 application running on the 32-bit Windows OS:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\ACE

• For MS Access 2007 application running on the 64-bit Windows OS:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\ACE

• For MS Access 2010 application running on the 32-bit Windows OS:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\ACE

• For MS Access 2010 application running on the 64-bit Windows OS:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\ACE

• For MS Access 2013 application running on the 32-bit Windows OS:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\15.0\Access Connectivity Engine\Engines\ACE

• For MS Access 2013. Mainly which are running on the 64-bit Windows OS:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\15.0\Access Connectivity Engine\Engines\ACE

• For MS Access 2016 application running on the 32-bit Windows OS:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\Access Connectivity Engine\Engines\ACE

• For MS Access 2016 application running on the 64-bit Windows OS:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\ACE

Note that this method changes the registry setting for all applications that use Microsoft Jet database engine version 4.0.

Method 2# Using Setoption To Change Maxlocksperfile Temporarily

In the DBEngine object, there is a SetOptionmethod. You can use this method to change the value of MaxLocksPerFile in the code for a temporary period.

Well, this SetOptionmethod overrides the value of Microsoft Jet database engine keys in the system registry only for a temporary period of time.

Your assigned new value will remain intact until you modify it again or until and unless the DBEngine objects are closed.

The below-given sample code uses the DAO (Data Access Objects).

For executing this code properly you need to reference the Microsoft DAO 3.6 Object Library.

• Firstly open the Visual Basic Editor then hit the tools> references options from the menu.
• In the opened window of References, make sure that the check box of Microsoft DAO 3.6 Object Library is selected.

 Note:

Changes done in the MaxLocksPerFilesetting by making use of the SetOption method is only available via the current session of DAO (Data Access Objects).

Queries that are mainly executed through the user interface of MS Access can still use registry settings.

The following code sample will help you to sets the value of MaxLocksPerFile to 200,000; right before executing the update task within the transaction:

VBCopy
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 you are getting this Access there isn’t enough memory to perform this operation error meanwhile running the action query. In that case, you need to modify the query and set its UseTransactionproperty to No.

If a previously-stored action query is causing this error then set NO in the property of UseTransaction.

Remarks:

Be careful because you can’t undo your changes if you encounter any error or problem meanwhile the query execution.

1. Open your Access query in the Design view.
2. Now click on the View>Properties option from the menu.
3. Make a tap over the empty spaces present in the upper section of the query window to view the dialog box of Query Properties.
4. Set the property of UseTransaction to No.
5. Now save it’s time to save the query and then close it.

Method 4# Use Code For Adding Field In Access Design View

 If you are getting the same there is not enough memory error whenever you are trying to add a new field in the table design view.

Then try the following code to fix this issue. The given code is quite basic and you will love adding this to make the code snippet powerful.

Here is the code:

Sub AddFieldToTable(ByVal tableName As String, fieldName As String, fieldType As Integer)

Dim objDB As Database

Dim objTableDef As TableDef

Dim objField As Field

Set objDB = CurrentDb

Set objTableDef = objDB.TableDefs(tableName)

‘add the new field

With objTableDef

Set objField = .CreateField(fieldName, fieldType)

.Fields.Append objField

‘You can also set other properties for the field (such as null values allowed etc) here

If fieldType = dbText Then

.Fields(fieldName).AllowZeroLength = False

End If

End With

Set objField = Nothing

Set objTableDef = Nothing

Set objDB = Nothing

End Sub

Method 5# Increase Paging File (Virtual Memory)

This specific error “there isn’t enough memory to perform this operation” also comes when your system RAM is not well capable of handling all the requests.

When such an issue arises your PC starts using the paging files (virtual memory) for the temporary storage of the additional data.

This virtual memory or paging file is not correctly managed. Thus you need to correctly manage it manually.

Here is how you can perform this task:

• In the Start menu search box type performance and press the enter button.
• Hit the Adjust the appearance and performance of the Windows option.

• In the opened window of the performance option, go to the Advanced tab.
• Hit the Change button and this opens the virtual memory screen.

• Choose the disk (SSD type disk or preferably not the C:) after then choose the Custom size option. In the given two boxes of final and initial dimensions put the exact same size value in the MB.

• Hit the Set option, and then tap the OK.

• To see all your applied changes restart your PC.

Method 6# Update Windows

If you are a Windows 10 user then go to the Settings app and check for the updates which are still not installed on your PC. This Windows update is very helpful to fix many bugs and issues arise on your PC. This will put a positive impact on the RAM of your PC.

So if your system is showing such “there is not enough memory” error then it will also get resolved after making windows update.

Method 7# Add RAM

If still you are getting “there is not enough memory to complete this operation” error then it could be due to lack of RAM. To deal with such a situation you need to buy and install a new memory module on your PC.

Wrap Up:

I hope you found this guide useful to fix Access there is not enough memory to complete this operation error.

Let us know whether your problem is solved or not by using the above-given fixes by sharing your experiences in the comment section.