Hi Stephen,
Call the ReplaceStr function in the error handler of your existing code. For
example, here is a procedure that I have for inserting country names into a
JET (ie. "Access") table. The script, as currently shown, chokes on six
cities.
Notes:
You can grab a copy of the script from here:
http://www.sql-scripts.com/members/ScriptDetails.aspx?S_ID=74
You may need to create an account to log in to the above site. The accounts
are free.
I removed some of the normal indenting in the code, to help prevent word
wrap in this newsgroup reply from splitting a line of VBA code.
Option Compare Database
Option Explicit
Sub CreateCountries()
On Error GoTo ProcError
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strErrors As String
Dim lngRecords As Long
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblSQLScript", dbOpenTable)
Do Until (rs.BOF Or rs.EOF) = True
strSQL = rs("SQLStatement")
db.Execute strSQL, dbFailOnError
lngRecords = lngRecords + 1
rs.MoveNext
Loop
ExitProc:
If Len(strErrors) > 0 Then
MsgBox strErrors, vbInformation, _
"The following records were not inserted due to problems", _
"Errors Encountered..."
Else
MsgBox "(" & lngRecords & ") records were imported.", _
vbInformation, "Done!"
End If
' Cleanup
On Error Resume Next
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
Exit Sub
ProcError:
Select Case Err.Number
Case 3075
strSQL = SQLFixup(strSQL)
Resume
Case Else
strErrors = strErrors & vbCrLf & "Error " & Err.Number & ": " &
Err.Description
Resume Next
End Select
End Sub
' How To Handle Quotes and Pipes in Concatenated SQL Literals
'
http://support.microsoft.com/?id=178070
' The ReplaceStr function has been modified to skip the first and
' last apostrophes and any apostrophees that enclose a comma in
' the INSERT statement (ie. ',').
Function ReplaceStr(TextIn, ByVal SearchStr As String, _
ByVal Replacement As String, _
ByVal CompMode As Integer)
Dim WorkText As String
Dim Pointer As Integer
Dim i As Integer
Dim intFirstApostrophe As Integer
Dim intLastApostrophee As Integer
If IsNull(TextIn) Then
ReplaceStr = Null
Else
WorkText = TextIn
' Locate the first and last apostrophes. Note: The InStrRev function
' is not available in Access 97. This functionality can be coded, but
' it requires writing a specialized function.
intFirstApostrophe = InStr(Pointer + Len(Replacement), _
WorkText, SearchStr, CompMode)
intLastApostrophee = InStrRev(WorkText, SearchStr, -1, CompMode)
' Find the next pointer
Pointer = InStr(intFirstApostrophe + Len(Replacement), _
WorkText, SearchStr, CompMode)
Do While Pointer < intLastApostrophee
'Debug.Print "Next Pointer = " & Pointer
'Debug.Print WorkText
' Is the next character a comma followed by an apostrophe?
' If so, don't replace this apostrophe.
If Mid$(WorkText, Pointer + 1, 2) = ",'" Then
Pointer = Pointer + 1
Else
WorkText = Left(WorkText, Pointer - 1) & Replacement & _
Mid(WorkText, Pointer + Len(SearchStr))
intLastApostrophee = InStrRev(WorkText, SearchStr, -1, CompMode)
End If
' Find the next pointer
Pointer = InStr(Pointer + Len(Replacement), _
WorkText, SearchStr, CompMode)
' Debug.Print "============================="
Loop
ReplaceStr = WorkText
End If
'Debug.Print WorkText
End Function
Function SQLFixup(TextIn)
SQLFixup = ReplaceStr(TextIn, "'", "''", 0)
End Function
Tom Wickerath
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
