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.
- Open a new workbook;
- 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.
- 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
- 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
- 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
- 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
- Complete the Update worksheet as follows, starting at cell A8:
- 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