A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

sql string as field data; ado?



 
 
Thread Tools Display Modes
  #1  
Old July 9th, 2009, 05:24 PM posted to microsoft.public.access.queries
robert.waters
external usenet poster
 
Posts: 15
Default sql string as field data; ado?

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.


Thanks!
  #2  
Old July 9th, 2009, 09:01 PM posted to microsoft.public.access.queries
MGFoster
external usenet poster
 
Posts: 653
Default sql string as field data; ado?

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-----
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:36 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.