8 Epic Solutions: Fix Access ‘There Isn’t Enough Memory to Perform This Operation’ Error

Imagine you are working on an important project in Microsoft Access, and all of a sudden, you come across the dreaded error message “There isn’t enough memory to perform this operation.” Frustrating, right? Well, in this blog, I will converse insights into this error, its common causes, and other imperative information including the troubleshooting methods to ensure smooth operations in your MS Access database.

Besides, I will also mention the versatile Access Repair Tool to fix database corruption.

Quick Fixes

• 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
• Repair Corrupt Database

What Does Access There Is Not Enough Memory to Complete This Operation Error Mean?

This error message typically indicates that the MS Access program has encountered a situation where it needs extra system memory (RAM) than is currently available to execute a specific operation.

Circumstances Under Which You Can Receive This Error

• While performing any operation with a huge number of page locks on the Access table.
• When you execute the action query on a large table you may get MS Access out of memory error. After the occurrence of this error message, all the data changes that the action query has done will revert back.

Why This Error Occur?

This warning message can occur due to various causes, including:

1. Insufficient System Memory- When the PC may not have sufficient space (RAM) to handle the large datasets operation.
2. Running Complex Queries- Complex queries, inefficient database design, or large data sets can sometimes lead to Access needing extra memory than is available.
3. Exceeding MaxLocksPerFile value– When the page lock exceeds the MaxLocksPerFile value which is important for transactions.
4. Due to Memory Leaks- If there’re memory leaks in the MS Access app or other programs running on your PC, it can cause a gradual depletion of available memory over time.
5. Corrupted Database– Severe or partial corruption in the Access database can trigger memory-related errors including there isn’t enough memory error.
6. Other Reasons- Hardware issues, lack of virtual memory, or failure of Windows update.

How to Fix Microsoft Access There Isn’t Enough Memory to Perform This Operation Error?

You can try the following workarounds to troubleshoot this message:

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 to increase 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.

Also Read: Fix Access “The Compact And Repair Operation Has Been Cancelled” Error

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 are 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 set 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 Microsoft 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.

• Open your Access query in the Design view.
• Now click on the View>Properties option from the menu.
• Make a tap over the empty spaces present in the upper section of the query window to view the dialog box of Query Properties.
• Set the property of UseTransaction to No.

• Now 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 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

That’s it.

Also Read: Fix “Microsoft Jet Database Engine Stopped The Process….” Error

Method 5- Increase Paging File (Virtual Memory)

This error message also comes when your system RAM is not 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 to Fix ‘There Isn’t Enough Memory to Perform This Operation’

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 updating Windows OS is very helpful to fix many bugs and issues that arise on your PC. This will put a positive impact on the RAM of your PC.

So if your system is showing the same error then it will also get resolved after making a 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 a lack of RAM. To deal with such a situation you need to buy and install a new memory module on your PC.

Method 8- Repair Corrupt Database

If the error has occurred due database corruption, you can use the Access free tool Compact and Repair to tackle the situation.

Here’s how you can do so:

• Open the file that shows the error.
• Go to the File>> Info option.
• After that, click on Compact & Repair Database.

This will create a copy of a compacted and repaired db in the same location.

Sometimes, the Access database file gets severely corrupted due to various unexpected reasons that Compact and Repair can’t fix it. In such instances, it is advisable to use the Access Repair Tool to fix severe corruption in the database. Whether it is .accdb or .mdb, it can fix corruption in both database formats with 100% integrity.

To download this tool, click on the below button.

* By clicking the Download button above and installing Stellar Repair for Access (14.8 MB), I acknowledge that I have read and agree to the End User License Agreement and Privacy Policy of this site.

Also Read: Fixes For Resolving “Access Database Too Large” Issue

How Can I Prevent MS Access Out of Memory Error?

By implementing the below strategies, you can reduce the chance of encountering this error.

Split the Databases– Split your large database into a front-end & a back-end.

Compact and Repair Database- Frequently compact and repair your database to avoid any type of corruption issues.

Limit Data in Forms or Reports- Use filters and parameters to restrict the displayed forms/reports, especially when handling large datasets.

Backup– Always create a backup of your important Access database file to avoid any data loss under unexpected situations.

Upgrade Hardware- Ensure to upgrade your computer’s hardware, including RAM for sufficient hardware resources.

Wrap Up

So, try all the solutions mentioned in this blog to monitor and fix the Microsoft Access there isn’t enough memory to perform this operation error. If the problem continues, it may be worth seeking help from the Microsoft support team for better assistance to work seamlessly in the database.

References:

• https://support.microsoft.com/en-au/office/compact-and-repair-a-database-6ee60f16-aed0-40ac-bf22-85fa9f4005b2

Still having issues? Fix them with this Access repair tool:

This software repairs & restores all ACCDB/MDB objects including tables, reports, queries, records, forms, and indexes along with modules, macros, and other stuffs effectively.

1. Download Stellar Repair for Access rated Great on Cnet (download starts on this page).
2. Click Browse and Search option to locate corrupt Access database.
3. Click Repair button to repair & preview the database objects.

Pearson Willey

Pearson Willey is a website content writer and long-form content planner. Besides this, he is also an avid reader. Thus he knows very well how to write an engaging content for readers. Writing is like a growing edge for him. He loves exploring his knowledge on MS Access & sharing tech blogs.