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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|