Apostrophe problems

[CopperWire]

While reading in a text file using the scipting.text string, I get an error when and apostrophe is encountered. Any ideas how to ingnore apostrophe's?

 

[EBGreen]

Can you show the pertinent code?

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[CopperWire]

Getting RunTime Error 3075 when it encounters an apostrophe.

Here's the code:
Sub GetElecRawdata()

Dim strLine As String
Dim strBudYr As String
Dim strDist As String
Dim strRegion As String
Dim strResZn As String
Dim strSerAreaCd As String
Dim fso As New Scripting.FileSystemObject
Dim ts As Scripting.TextStream
Dim fd As FileDialog
Dim vrtSelectedItem As Variant

Set fd = Application.FileDialog(msoFileDialogFilePicker)
Application.FileDialog(msoFileDialogFilePicker).InitialFileName = ("W:\Work_Planning\PEP Update\*.txt")
With fd
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
Set ts = fspenTextFile(vrtSelectedItem)
Next vrtSelectedItem
Else
End If
End With
ts.SkipLine 'skip first line in text file (headings)

Do Until ts.AtEndOfStream
strLine = ts.ReadLine 'read line in .txt file
intpos = InStr(strLine, Chr(9)) 'finds first comma
'inStr - finds charitures in a line
strBudYr = Left(strLine, intpos - 1) 'find text until tab & don't bring me the tab (tab delimited text file)
intpos2 = InStr(intpos + 1, strLine, Chr(9)) 'only use on middle records, don't use for last record
intpos3 = intpos2 - intpos 'only use on middle records, don't use for last record
strDist = Mid(strLine, intpos + 1, intpos3 - 1) 'start after first tab
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strRegion = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strResZn = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strSerAreaCd = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strSerArea = Mid(strLine, intpos + 1, intpos3 - 1)
strSQL = "INSERT INTO ecplrawdata( [Budget Year], [District], [Region], [Resource Zone], [Service Area Code]) Values ('" & strBudYr & "','" & strDist & "','" & strRegion & "','" & strResZn & "','" & strSerAreaCd & "','" & strSerArea & "','" & strOpsZn & "','" & strArea & "')"

CurrentDb.Execute strSQL, dbFailOnError

Loop

Set fso = Nothing
Set fd = Nothing
Set ts = Nothing

End Sub

 

[CopperWire]

Found a solution!!

Use the following (If-EndIf) code for the fields that may contain an apostrophe:

strWRDesc = Mid(strLine, intpos + 1, intpos3 - 1)
If strWRDesc = "*'*" Then
strWRDesc = Replace(strWRDesc, "'", "")
End If

This will remove the apostrophe from the text string.

Still not sure why an apostrophe would throw an error during an insert statement into an Access table. If someone could help with that??