Darragh Murray

Running SQL from Visual Basic Code – Apostrophe problems!

—

by

Darragh
in ,

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!

Comments

2 responses to “Running SQL from Visual Basic Code – Apostrophe problems!”

  1. David Young Avatar
    David Young

    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.

  2. daz Avatar
    daz

    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!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.