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;Persist 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.