iiop.blogg.se - Excel vba on error or if escape

StrSQL = "Insert into tblCrewMember (LastName) values ('" & strCriteria & "')" StrCriteria = Sheets("Update").Range("B10") StrWord = Left(strWord, x - 1) & Chr(39) & Chr(39) & Right(strWord, Len(strWord) - (x)) X = InStr(x + 2, strWord, "'") 'Find position of apostrophes

StrConnectionstring = "DRIVER= SERVER=" & strServer & " Trusted_Connection=yes DATABASE=" & strDBase 'Windows authenticationįunction fRemoveApostrophe(strWord As String)

Public connDB As New ADODB.Connectionĭim strServer, strDBase, strUser, strPWD As String Use the menu items >Tools >References to reference ADO libraries.Ĭopy the code below into the module.

Open the code window and insert a module.

These fields will be used to build a connection string to SQL Server.

Name the first sheet “Update” and complete as follows, using your own database name, etc.

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. Thus, O”Dowd is acceptable to the database. 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):Īn escape character is needed, being a double apostrophe instead of a single one. The term “database” here applies to “industrial-strength” databases like SQL Server and Oracle.Īn example of the workbook used in this exercise can be found here. If not, please Google “Excel Developer Tab” or “Excel Code Window”.

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