I was doing some recordset juggling in vba and out of habit I usually close every object I open up explicit using the .close method on that object.
For example if I open up a database using something like this
Dim db as dao.database
Set db = currentDb
I will close it at the end of the function or procedure
Db.close
Set db = nothing
When using transactions, you make use of the workspace collection and you open it along the lines of
Dim myWrk as dao.workspace
Set myWrk = DBEngine.Workspaces(0)
As usual, I want to close this at the end of the procedure along the lines of
myWrk.close
set myWrk = nothing
This is were I went wrong
I was looping through a DAO recordset to check for conditions then make updates to a table. I wanted to make sure things didn’t screw up half way through so implemented some transactions, and then I started getting an error message
Run-Time Error ‘3420’:
Object Invalid or No Longer Set
Specifically when I tried to use the .moveNext method of my recordset.
I began to tear my hair out! Google talks about updating my jet version, but that wasn’t the problem at all – I knew that I had the most up to date Jet engine.
It took me some time and a bit of research but I figured out it was happening when I made a call to another procedure while inside the recordset. Looking through the code, and nothing was wrong. I even pasted the code of the sub-procedure into my main procedure (minus all the variable setting and clean up objects stuff) and it work. Something was up in my sub procedures.
Troubleshooting Potential Errors when Using Transactions in Ms Access – Do NOT Close the Workspace!
I was doing some recordset juggling in vba and out of habit I usually close every object I open up explicit using the .close method on that object.
For example if I open up a database using something like this
Dim db as dao.databaseSet db = currentDb
I will close it at the end of the function or procedure
Db.closeSet db = nothing
When using transactions, you make use of the workspace collection and you open it along the lines of
Dim myWrk as dao.workspaceSet myWrk = DBEngine.Workspaces(0)
As usual, I want to close this at the end of the procedure along the lines of
myWrk.closeset myWrk = nothing
This is were I went wrong
I was looping through a DAO recordset to check for conditions then make updates to a table. I wanted to make sure things didn’t screw up half way through so implemented some transactions, and then I started getting an error message
Run-Time Error ‘3420’:Object Invalid or No Longer Set
Specifically when I tried to use the .moveNext method of my recordset.
I began to tear my hair out! Google talks about updating my jet version, but that wasn’t the problem at all – I knew that I had the most up to date Jet engine.
It took me some time and a bit of research but I figured out it was happening when I made a call to another procedure while inside the recordset. Looking through the code, and nothing was wrong. I even pasted the code of the sub-procedure into my main procedure (minus all the variable setting and clean up objects stuff) and it work. Something was up in my sub procedures.
It was eventually a comment in this article on transactions at Allen Browne’s excellent Ms Access tips website – I was explicitly closing the workspace. This is apparently a no-no. I deleted the .close line of code, and voila, it all worked!