What is my SQL query blowup?

[Tim Anderson]

I am using VBA to query an Access database for a search and replace function in Word. My query works fine most of the time but when the following characters are in the access database it goes up my query. [], (),  , *, [snip], {snip} and ^m.

I have used other languages and databases where I had to use of function to reformat the text before it went into a table so that it didn't have any banned characters. Then I would use a different function to put the text back into the original format.

Is there a function like that in VBA or is something else going on?
Also, how do I get my code to format properly? When I hit preview it stripped out all of my formatting tabs.

This is my code:


Sub ReplaceBritishTextWithEnglishUsingAccessTable()
Dim conn As Object
Dim rs As Object
Dim strcon As String
Set Conn1 = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
strcon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=N:\Docs\VBAMacrosandCustomizations\SearchAndReplaceInWord.accdb;Pers ist Security Info=False;"
Conn1.Open (strcon)
rs.Open "SELECT * FROM WordDocReplacementText WHERE WordType = ""BritToUsEng"" ORDER BY WordType", Conn1, 3
With rs
Do Until .EOF
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = rs("OriginalWord")
.Replacement.Text = rs("CorrectedWord")
End With
Selection.Find.Execute Replace:=wdReplaceAll
.MoveNext
Loop
End With
rs.Close
Conn1.Close
End Sub

[PhilS]

I am using VBA to query an Access database for a search and replace function in Word. My query works fine most of the time but when the following characters are in the access database it goes up my query. [], (),  , *, [snip], {snip} and ^m.

I don't see any reason why these characters would create problems with this SQL query.
However, at least the ^ character in combination with other characters is used by Word to identify special formatting marks in find/replace operations, which might cause unexpected results of your find and replace action in Word.

If this doesn't help, please describe the problem you are facing more clearly.