Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change of table name in access where Excel obtains pivot table inf | Excel Programming | |||
Write data to Access table with INSERT when table has auto number | Excel Programming | |||
Importing data from Access to Excel, but I need to vary the table from Access | Excel Programming | |||
Multiple MS Access table sources for pivot table | Excel Discussion (Misc queries) | |||
Adding Records to Access Table | Excel Programming |