Adding company to an Access table
 

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

Adding company to an Access table
 

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" wrote:

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

Adding company to an Access table
 

Thanks - that works perfectly

"joel" wrote:

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" wrote:

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