Design advice SQL or QueryDef

[David G.]

I've built a form to dynamically create SQL statements. My original
idea was to save the SQL Statements into a table ("tblCustomSQL") as a
string. (Table has 2 fields, "Name", and "SQL".) I'm using an "INSERT"
SQL statement to add records to tblCustomSQL.

I'm running into trouble with nested quotation marks. If the custom
SQL statement is formatted correctly, the quotations in the custom SQL
interfere with the INSERT statement. I've tried encoding the SQL
statement with markers (QUOTE - in place the multiple quotations),
but decoding is very messy.

I'm thinking it might be better create a QueryDef from the SQL.

Would greatly appreciate any suggestions or recommendations for how to
proceed.
THANKS!
David G.

 

[Allen Browne]

So you are building a SQL statement as a string, and storing in in table?

You will want to double-up any quote marks in the SQL statement in your
string:
strSql = Replace(strSql, """", """""")
Then store it in the table, and you can operate on it without needing to
change them again later.

Not sure if this will be any help:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html

 

[Armen Stein]

So you are building a SQL statement as a string, and storing in in table?

You will want to double-up any quote marks in the SQL statement in your
string:
strSql = Replace(strSql, """", """""")
Then store it in the table, and you can operate on it without needing to
change them again later.

Not sure if this will be any help:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html

I put a detailed description of creating SQL Statements in VBA in
Chapter 15 of Access 2007 VBA Programmer's Reference (Wrox). It
includes an explanation of quoting techniques. Even if you don't buy
the book, you can download the code at:
http://www.wrox.com/WileyCDA/WroxTi...nce.productCd-0470047038,descCd-DOWNLOAD.html

See the file for Chapters 8, 9 & 15. In that Chamber application,
there's also some code to dynamically replace the Where clause in SQL
Statements. It's much better to use a query definition to store the
main query instead of building it all up in code. Then you can just
alter the Where and Order By clauses when you're ready to use it.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com