Problem inserting records with apostrophies

[Rahel]

I have an import routine in Access2000 that keeps failing when it encounters records that contain apostrophies in it.
When I get rid of the apostrophies, the import routine works.

(I am working with text fields.)

Has anyone come across this problem or knows about a fix for this to allow for apostrophies.

 

[scking]

This is NOT a bug but characteristic of SQL. Since SQL uses the apostrophe, comma, etc. they must be properly translated into a SQL statement before SQL can be used to INSERT or UPDATE or whatever the table. You will need to search for the appropriate method of including these in SQL and create a text parsing routine to build the correct string. I believe the apostrophe needs to be a double apostrophe such as ''.

PSEUDO CODE ONLY

If InStr(1, strText, "'&quot Then
strText = GetNewText(strText)
End If

Public Sub GetNewText(strText As String) As String

Dim intStart As Integer
Dim intPtr As Integer
intStart = 1

'Create a loop to search for the apostrophes
' or other special characters
' DoWhile intPtr <> 0

' Find the location of the apostrophe
intPtr = Instr(1, strText, &quot;'&quot

'Insert another apostophe to make it compatible
' with SQL text
' strText = BeginningPart & &quot;'&quot; & EndingPart

'Start searching for apostrophes from where the
' last one was inserted
intStart = intPtr + 1

Loop


Please consider this pseudo only. But it should lead you down the correct path.

Steve King


End Sub
Professional growth follows a healthy professional curiosity