Others

How to Fix Microsoft Access Error Object Invalid or No Longer Set

Rana GarciaRana Garcia Nov 3, 2026 871 views

Question details

The user needs to fix an error occurring during the execution of VBA code that processes database records.

How to Fix Microsoft Access Error Object Invalid or No Longer Set
Product
Microsoft Access
Device & OS
not provided
Scenario
Running VBA code to update or append database records by looping through a DAO recordset.
Observed behavior
The application throws an Object invalid or no longer set error because a DAO recordset is being closed prematurely inside the loop while the code is still actively referencing it.
Before you start

Before modifying your VBA code, ensure you create a backup copy of your Microsoft Access database to prevent accidental data loss while testing loops and queries.

Solution 1Recommended

Correct the DAO Recordset Loop and Closure

Fix the logic error in your VBA code by ensuring the recordset remains open throughout the loop and is closed only after all records have been processed.

The 'Object invalid or no longer set' error happens when your code attempts to manipulate an object that has been closed or removed from memory. In recordset loops, closing the recordset before the loop finishes is the most common cause.

1
Locate the recordset loop in your VBA code

Open the VBA editor in Microsoft Access and navigate to the module or form code containing your recordset operations. Find the 'Do While' or 'For' loop where your recordset (e.g., rs_2) is processed.

2
Move the Close statement outside the loop

Look for the 'rs_2.Close' and 'Set rs_2 = Nothing' statements. Remove them from inside the loop block and paste them immediately after the 'Loop' or 'Next' statement.

3
Ensure correct recordset navigation

Verify that your loop contains an 'rs_2.MoveNext' command inside the loop block so that it properly advances through the records without causing an infinite loop.

Correct the DAO Recordset Loop and Closure
Proper Initialization: Ensure that your DAO database and recordset variables are correctly declared and assigned using the 'Set' keyword before the loop begins.
Free Microsoft Office alternative

Looking for a Reliable and Free Office Suite?

If you frequently encounter complex macro issues in Microsoft Office, consider experiencing the simplicity of WPS Office. It is a highly compatible, free, and lightweight Office suite that handles your documents, spreadsheets, and presentations efficiently without overwhelming you with complicated backend database errors.

  1. 1. Download and Install: Visit the official WPS Office website to download the free installation package for your Windows, Mac, or Linux device.
  2. 2. Open Your Office Files: Easily open your existing Word documents, Excel spreadsheets, and PowerPoint presentations directly in WPS Office with full format retention.
  3. 3. Analyze Data Without Code: Use WPS Spreadsheet's comprehensive built-in formulas, pivot tables, and conditional formatting to process large datasets without relying on intricate VBA scripts.
Free to use with a familiar, modern user interface.Fully compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formats.Lightweight software that loads quickly and consumes minimal system resources.Powerful spreadsheet data management tools for analysis without complex VBA coding.
microsoft office alternative - wps office

Frequently Asked Questions

What does Object invalid or no longer set mean in Access VBA?

This error occurs when your VBA code attempts to interact with an object variable, such as a recordset or database object, that has already been closed, destroyed, or set to 'Nothing' earlier in the code sequence.

Why is CurrentDb.Execute preferred over a DAO recordset loop?

Using CurrentDb.Execute with an update or append query allows the database engine to process all records in a single batch. This is significantly faster and consumes less memory compared to pulling each record into a DAO loop and processing them sequentially.

What is the purpose of DbFailOnError in Access VBA?

Adding the DbFailOnError argument to your CurrentDb.Execute command forces Access to roll back the entire data operation if an error occurs (like a key violation or lock issue) and triggers a runtime error, preventing partial updates and maintaining data integrity.