How to Escape Quoted Strings for SQL Statement used in Database via Excel VBA

SQL Server uses pairs of single quotes to identify the start and end of a string. Inserting ‘Mrs Brown’s Boys’ into a database table will fail since the three single quotes imply two strings, one of which is incomplete. An escape character is required for the apostrophe after Brown. This article explores the use of a customised VBA function to resolve this anomaly.

This article assumes the reader has the Developer ribbon displayed and is familiar with the VBA Editor. If not, please Google “Excel Developer Tab” or “Excel Code Window”.

The term “database” here applies to “industrial-strength” databases like SQL Server and Oracle.

An example of the workbook used in this exercise can be found here.

The SQL String

Inclusion of apostrophes (or single quotes) inside an SQL statement provides the following error returned from the database manager (for the name O’Dowd in this case):

An escape character is needed, being a double apostrophe instead of a single one. Thus, O”Dowd is acceptable to the database. O’Dowd is not.

The Function

Where capture fields might conceivably contain an apostrophe, a custom function can be built to fire before update, replacing the single quote with a double one.

1. Open a new workbook;

2. Name the first sheet “Update” and complete as follows, using your own database name, etc. These fields will be used to build a connection string to SQL Server.
3. Open the code window and insert a module. Use the menu items >Tools >References  to reference ADO libraries.

Copy the code below into the module. This connects to the database.

Public connDB As New ADODB.Connection
Public rs As New ADODB.Recordset
Public strSQL As String
Public strCriteria As String
Sub ConnectDatabase()

    If connDB.State = 1 Then connDB.Close
    On Error GoTo ErrConnect
    
    Dim strServer, strDBase, strUser, strPWD As String
    
    strServer = Sheets("Update").Range("B2")
    strDBase = Sheets("Update").Range("B3")
    strUser = Sheets("Update").Range("B4")
    strPWD = Sheets("Update").Range("B5")
    If strPWD > "" Then
        strConnectionstring = "DRIVER={SQL Server};Server=" & strServer & ";Database=" & strDBase & ";Uid=" & strUser & ";Pwd=" & strPWD & ";Connection Timeout=30;"
    Else
        strConnectionstring = "DRIVER={SQL Server};SERVER=" & strServer & ";Trusted_Connection=yes;DATABASE=" & strDBase  'Windows authentication
    End If
    connDB.ConnectionTimeout = 30
    connDB.Open strConnectionstring
Exit Sub
ErrConnect:
    MsgBox Err.Description
End Sub

4. Add the function into the module:

Function fRemoveApostrophe(strWord As String)
   Dim n As Integer
   Dim x As Integer
   x = 0
   For n = 0 To 100
        x = InStr(x + 2, strWord, "'")  'Find position of apostrophes
        If x = 0 Then Exit For
        If x > 0 Then
            strWord = Left(strWord, x - 1) & Chr(39) & Chr(39) & Right(strWord, Len(strWord) - (x))
        End If
    Next n
    fRemoveApostrophe = strWord
End Function

5. Ignore the function.

Sub IgnoreFunction()
    Call ConnectDatabase
    strCriteria = Sheets("Update").Range("B10")
    strSQL = "Insert into tblCrewMember (LastName) values ('" & strCriteria & "')"
    MsgBox strSQL & ". This SQL entry will fail; note the three apostrophes."
    Debug.Print strSQL
    'connDB.Execute (strSQL)
End Sub

6. Use the function

Sub UseFunction()
    Call ConnectDatabase
    strCriteria = Sheets("Update").Range("B15")
    strCriteria = fRemoveApostrophe(strCriteria)
    strSQL = "Insert into tblCrewMember (LastName) values ('" & strCriteria & "')"
    MsgBox strSQL & ". This SQL entry will succeed, and appear in the datatable as O'Dowd."
    Debug.Print strSQL
    'connDB.Execute (strSQL)
End Sub

7. Complete the Update worksheet as follows, starting at cell A8:

8. Assign the buttons to macros IgnoreFunction and UseFunction repectively

The Results

A message box will show the results; no database is physically updated in this exercise but, if you wish to do so, ensure the field names are compatible with your database, and add use the VBA statement conndb.execute(strSQL)

Recovering from Excel crashes

Excel is prone to crash when your computer is running out of resources.During the writing of this exercise, Excel’s spreadsheet, as yet unsaved, froze. The Code window was partially responsive, allowing the closure of the workbook as a whole.  As it turned out, the workbook re-opened normally with the content and code complete. Had the temporary and source files been (all too frequently) damaged, the work would have had to be redone in the absence of a tool to resolve xlsx damage. It was of little significance in this case, but could be a potential disaster for larger workbooks.

Author Introduction:

Felix Hooker is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including recover rar and sql recovery software products. For more information visit www.datanumen.com