Excel to Access using ASP

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

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
 
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 said:
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
Click to expand...
apostrophe (') in them.

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

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.

--
 
Back
Top