Removing unwanted characters, mostly single quotes, is a replacement for function and implementation

Question

I just tested my database, and I realized that I run into problems wherever the text entry in my database contains the character ' (single quote). My solution at the moment is that before any .execute string operations, I call escape(string, "'", " "'" ") .

A generalized example below:

 qr = "INSERT INTO tblExample VALUES ( " & "'" & me.testparam & "'" & ");" qr = Replace(qr, "'", " "'" ") db.execute qr 'also tried qr = "INSERT INTO tblExample VALUES ( " & "'" & replace(me.testparam,"'"," ") & "'" & ");" 

This was what I thought was the right workaround to prevent errors from values ​​like Tourette's .

There are two problems with this. First of all, it does not work. Secondly, I have more than 50 locations in the code in my application, where I call the db.execute qr operator, where qr is a string that could potentially contain a single quote. I need the field in the table to contain a single quote, so I can't just replace it with space or something similar.

Question with two parts:

• Is there a better solution than going through all the code that calls Replace for each line that should be executed as a request?

• Why is my current implementation not working? - I still get a syntax error in the query expression, even when I avoid a single quotation mark with a space.

Answer 1

First check these two lines.

 "VALUES ( " & "'" & me.testparam & "'" & ");" "VALUES ( '" & me.testparam & "');" 

Both will create the same line. The difference for me is that my brain understands the second version faster.

Now, here's what the comments tell you to do ... replace each single quote in the original string with two single quotes. I added Debug.Print so that you can view the finished line in the Immediate window (go there with Ctrl + g) ... you can see the actual line, rather than trying to imagine what it looks like.

 qr = "INSERT INTO tblExample VALUES ( '" & _ Replace(Me.testparam, "'", "''" & "');" Debug.Print qr db.Execute qr, dbFailOnError 

Since I assumed that db is a variable of the DAO.Database object, I turned on the dbFailOnError option. You must include an error handler in your code to resolve any dbFailOnError problems.

When you encounter a problem with the VBA function in the request, go to the Immediate window and check the expression of the function there. This causes a compilation error "Expected: list separator or)":

 ? Replace("Tourette's", "'", " "'" ") 

But it works:

 ? Replace("Tourette's", "'", "''") Tourette''s 

I mentioned this because it is useful in general, and also because your title starts with "Reset unwanted characters, mostly single quotes." Therefore, if you want to remove / replace other characters, not single quotes, experiment in the Immediate window until you find the Replace() expression that works. Then use this expression in the query.

For example, if unwanted characters include line breaks ...

 MyString = "foo" & vbCrlf & "bar" : ? MyString foo bar ? Replace(MyString, Chr(13) & Chr(10), " ") foo bar 

Note. I used Chr(13) & Chr(10) instead of vbCrlf as the search target, because the db mechanism can use the Chr() function, but does not know about the named constant ( vbCrlf ).

Answer 2

Your request does not work because you did not say where to embed:

 Dim qd As QueryDef qr = "INSERT INTO tblExample (AText) VALUES ( [avalue] );" Set qd = CurrentDB.CreateQueryDef("",qr) qd.Parameters("avalue").Value = me.testparam qd.Execute dbFailOnError 

Answer 3

Another method is to define a quote as a constant (Const Quote = "" ") and use it to build SQL expressions. It is impossible to define a quote as Const Quote = Chr (34), because the definition of a constant cannot be based on a function, so you need to use four double quotes in the string. The third quote is what you keep, the second quote is to extract the third quote, and the first and last quote is because the value you assign is a string.

Then you can create SQL statements such as:

 SQL = SELECT * FROM tblSyndromes WHERE Syndrome = " & Quote & "Tourette's" & Quote & ";" 

Now it will not matter that your data has single quotes.

I do not use parameters, as if I scaled my database on sql server and converted my queries into pass-through queries, I cannot use parameters. I am rarely upscale, but I write all my code with this assumption. In addition, if your request does not work properly, how to find out what went wrong. If I have a variable called SQL, I can always print the SQL statement and run it in a new query to see what it does.