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!
Does Access actually require postgres-style double-quotes around column names? SQL Server normally doesn’t; but you could also try [] which is the other delimiter favoured by Microsoft DBs.
Anyway, dealing with s*** like that is your deserved punishment for using VB (and not even VB.NET!) in the year 2006.
As far as I know, that delimter doesn’t work in Jet SQL.
Access can take single ‘ ‘ delimters, but the way I propose is a functional way of doing it from code. Unfortunately, it seems that Jet inteprets the SQL different from VBA code than it does when using the normal query windows through its native interface. The reason why it is different, I shall have to find out!