Adding company to an Access table

[Daniel Bonallack]

This code is actually in Excel, and I have one specific question. This is
code that enters the value myStr into an Access table called MYTABLE

For i = 1 To 5
myStr = cells(i,1).value
Sql = "INSERT INTO MYTABLE VALUES (" & Chr(39) & myStr & Chr(39) & ")"
Connection.Execute Sql
Next i

This works (it's part of a larger procedure), unless the value myStr has an
apostrophe in it - can you tell me how to upload a value with an apostrophe
in it?

thanks
Daniel Bonallack

 

[joel]

Chr(39) is an aposophe. Any apostophe in the string has to be replace wit
two aposttophes


For i = 1 To 5
myStr = cells(i,1).value
mystr = replace(mystr,chr(39),chr(39) & chr(39))
Sql = "INSERT INTO MYTABLE VALUES (" & Chr(39) & myStr & Chr(39) & ")"
Connection.Execute Sql
Next i

instead your can use
mystr = replace(mystr,"'","''")

 

[Daniel Bonallack]

Thanks - that works perfectly

Chr(39) is an aposophe. Any apostophe in the string has to be replace wit
two aposttophes


For i = 1 To 5
myStr = cells(i,1).value
mystr = replace(mystr,chr(39),chr(39) & chr(39))
Sql = "INSERT INTO MYTABLE VALUES (" & Chr(39) & myStr & Chr(39) & ")"
Connection.Execute Sql
Next i

instead your can use
mystr = replace(mystr,"'","''")