Excel to Access using ASP
 

Hi
Anyone know an ASP script for transferring data from an Excel sheet to an
Access table, some of the fields of which contain names which have an
apostrophe (') in them.
I have tried all sorts of things but am getting tripped up by the apostrophe
which is used as a field container in the SQL :

"INSERT INTO tblResults Values(0,'" & rsExcel("FeisID") & "' , '" &
rsExcel("CompNo") & "' , '" & rsExcel("Entry") & "' , '" & rsExcel("Name") &
"' , '" & rsExcel("School") & "' , '" & rsExcel("Position") & "' , '0' , '"
& Done & "')"

The first field is the ID field of tblResults and need to be incremented
automatically. If I leave the first value out, I get an error along the
lines of "Number of query values and destination fields are not the same"
Also, if the name field is like Harry O'Hare, the thing fails...
Any ideas anyone? Many thanks

Jim

Excel to Access using ASP
 

Use replace(strValue,"'","''") to deal with the apostrophe problem.

Also, if the first field increments automatically, you should remove it from
both the Fields and the Values parameters.

Regards,
Wayne C.,

"Jim" wrote in message
...
Hi
Anyone know an ASP script for transferring data from an Excel sheet to an
Access table, some of the fields of which contain names which have an
apostrophe (') in them.
I have tried all sorts of things but am getting tripped up by the
apostrophe which is used as a field container in the SQL :

"INSERT INTO tblResults Values(0,'" & rsExcel("FeisID") & "' , '" &
rsExcel("CompNo") & "' , '" & rsExcel("Entry") & "' , '" & rsExcel("Name")
& "' , '" & rsExcel("School") & "' , '" & rsExcel("Position") & "' , '0' ,
'" & Done & "')"

The first field is the ID field of tblResults and need to be incremented
automatically. If I leave the first value out, I get an error along the
lines of "Number of query values and destination fields are not the same"
Also, if the name field is like Harry O'Hare, the thing fails...
Any ideas anyone? Many thanks

Jim

Excel to Access using ASP
 

Jim wrote:
Anyone know an ASP script for transferring data from an Excel sheet
to an
Access table, some of the fields of which contain names which have an

apostrophe (') in them.

"INSERT INTO tblResults Values(0,'" & rsExcel("FeisID") & "' , '" &
rsExcel("CompNo") & "' , '" & rsExcel("Entry") & "' , '" &
rsExcel("Name") &
"' , '" & rsExcel("School") & "' , '" & rsExcel("Position") & "' ,
'0' , '"
& Done & "')"


You may not need the recordset e.g. something like this:

INSERT INTO
FeisID, CompNo,
Entry, Name, School,
[Position], text_col, Done
SELECT
FeisID, CompNo,
Entry, Name, School,
[Position], '0' Done
FROM
[Excel 8.0;Database=C:\MyBook.xls;].[MySheet$];

Jamie.

--