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.database
Set db = currentDb

I will close it at the end of the function or procedure

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

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.

It was eventually a comment in this article on transactions at Allen Browne’s excellent Ms Access tips websiteI was explicitly closing the workspace. This is apparently a no-no. I deleted the .close line of code, and voila, it all worked!

Running SQL from Visual Basic Code – Apostrophe problems!

I recently encountered a problem that I’ve actually encountered millions of times before – trying to run an SQL string from VBA code which makes use of double apostrophe’s (“”). Naturally, VBA uses these to break from a string literal, but pure Jet-SQL uses them for functions like Format() and Nz().

So if you do something like this:

strSqlCode = “Select format([field1], “Currency”) from table1″
db.execute strSqlCode

You will get a nice friendly Access message indicating that your SQL is faulty. Surely that ain’t the case!

There are several different solutions and here is one I find the most handy – using chr$(34)!

Reformat your SQL like this:

strSqlCode = “Select format([field1], ” & Chr$(34) & “Currency” & Chr$(34) & “) from table1”
db.execute strSqlCode

And Viola!