Consulting

Results 1 to 2 of 2

Thread: What is my SQL query blowup?

  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

  2. #2
    Quote Originally Posted by Tim Anderson View Post
    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.
    Learn VBA from the ground up with my VBA Online Courses.

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
  •