Consulting

Results 1 to 2 of 2

Thread: What is my SQL query blowup?

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1

    What is my SQL query blowup?

    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
    Last edited by Aussiebear; 05-26-2022 at 05:17 PM. Reason: Added code tags to supplied code

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •