How To Escape Apostrophe In SQL Update Query

If you are looping a table with thousands of records, you’ll probably run into at least one that has an apostrophe in the field name.

Like “Mike’s” or “M’cormick”, or something else.

Anyway, here is one way to escape the string when you are doing your update query.

Option Compare Database

Sub YDriveLoop()
    '4/23/24 erik@loeblcomservices.com
    
    Dim rst As dao.Recordset
    Dim strTitle As String, strBatesNo As String
    Dim strUpdateSQL As String
    
    Set rst = CurrentDb.OpenRecordset("SELECT BegBates,[Folder/File],Title FROM YDrive")
    
    strTitle = "blank"
    
    Do Until rst.EOF
        strBatesNo = rst.Fields("BegBates")
        If rst.Fields("Folder/File") = "Folder" Then
            strTitle = rst.Fields("Title")
        End If
        
        
        strUpdateSQL = "UPDATE YDrive SET DrivePath= '" & SQLFixup(strTitle) & "' WHERE BegBates='" & strBatesNo & "'"
        
        CurrentDb.Execute (strUpdateSQL)
        
        Debug.Print "updating: " & strBatesNo
        rst.MoveNext
    Loop
    
    rst.Close
    Set rst = Nothing
    
End Sub

Function ReplaceStr(TextIn, ByVal SearchStr As String, _
    ByVal Replacement As String, _
    ByVal CompMode As Integer)

    Dim WorkText As String, Pointer As Integer
    
    If IsNull(TextIn) Then
        ReplaceStr = Null
    Else
        WorkText = TextIn
        Pointer = InStr(1, WorkText, SearchStr, CompMode)
        Do While Pointer <> 0
            WorkText = Left(WorkText, Pointer - 1) & Replacement & _
            Mid(WorkText, Pointer + Len(SearchStr))
            Pointer = InStr(Pointer + Len(Replacement), WorkText, _
            SearchStr, CompMode)
        Loop
        ReplaceStr = WorkText
    End If

End Function

Function SQLFixup(TextIn)

    SQLFixup = ReplaceStr(TextIn, "'", "''", 0)

End Function

Hope it helps.

By the way, if you got or are getting value from the VBA information, please click the "Donate" button to give me a small token of your appreciation, thanks!