How to Fix Microsoft Access Error Object Invalid or No Longer Set
Question details
The user needs to fix an error occurring during the execution of VBA code that processes database records.

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

Optimize Performance Using a Single Execute Query
Replace the resource-intensive recordset loop with a single append or update query to process data faster and simplify your VBA code.
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. Download and Install: Visit the official WPS Office website to download the free installation package for your Windows, Mac, or Linux device.
- 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. 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.

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.




