robert.waters wrote:
How might one escape an SQL string for insertion into a table?

I have a multi-user Access db, with users that create their own
queries that need to be synced to every other user's frontends. To
accomplish this, I had previously created a 'query repository' - the
frontends are updated with the current query set using a push/pull to/
from that repository (querydefs are copied around). Unfortunately due
to a limitation of Acess, this causes query Created and LastUpdated
dates to be lost (reset with the current date). So, I'd like to track
the queries in a table, including their sql source. However, I do not
know how to escape the sql text so that it doesnt pollute the sql
INSERT string. I am currently encoding it to base64, but i am sure
there is a better way.


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Please indicate the problems you are having. I tested insertion of an
SQL string into a table and had no problem. Perhaps you are referring
to the quotes problem, where the quotes have to be doubled? To solve
that problem you could just use something like this:

Replace(Replace(strSQL,"'","''"),"""","""""")

This will replace single quotes (') with 2 single quotes, and replace
double quotes (") with 2 double quotes.

You can also do this to a QueryDef:

Replace(Replace(CurrentDB.QueryDefs("query_name"). SQL,"'","''"),"""","""
"")

If you wish to maintain creation dates when you import queries just put
the dates in the query's Description property (right click the query and
select Properties from the pop-up menu). The property doesn't exist
until something is put in there. If you want to create the property
programmatically you'll have to use the CreateProperty method. You
retrieve the property like this:

strDesc = CurrentDb.QueryDefs("query_name").Properties("Desc ription")

HTH,
--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSlZMk4echKqOuFEgEQJrBACfYWujN8wGBa1X9jrRI0+R0s qTSWkAoJZ0
MO7pfWe24jb3BXihdo+bIQf2
=qD/i
-----END PGP SIGNATURE-----