Excel, ASP Classic, Imex, Mixed Data Types, & ConnectionStrings

(This post is in the 'Database' forum also, but there wasn't too much action there, so I thought I'd spread my net...)

Hello Group

I have a problem with a Classic ASP app, which is reading an Excel (2003) XLS file and printing some data as an online, onscreen report.

A couple of the columns are mixed - 1 in pariclar has some DATE values, (while some cells in the column are empty), and another column holds some INTEGER values (while some cells in that column are also empty).

Of course, it works beautifully on my localhost, but fails once on the DASP site if the first few column values are empty - classic 'mixed data' behaviour.

After reading lots of forums, and trying lots of connection strings - some of which just threw errors, and others which opened the file but returned null values for my mixed columns - I thought I'd ask what the best connection string is to use.

Here's a sample of two connection strings I've tried to date:

(This one opens the file OK, but returns nulls on mixed data - if the first values are empty)
1. strConnection = "Provider=MSDASQL.1;DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" & Server.MapPath("\_data_\book.xls") & ";DefaultDir=" & Server.MapPath("\_data_\") & ";Mode=ReadWrite;FIL=excel 8.0;DriverId=790;"

(This one throws a "Cannot start your application. The workgroup information file is missing or opened exclusively by another user. " error)
2. strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("\_data_\book.xls") & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

I've read somewhere that there may be some DLL permissions at the server which may be stopping option 2 above from working, but I think this 'IMEX' approach may be what I need.

Can anyone give me some clues as to the best approach to get around this particular problem?

There's almost too much info out on the Internet about this - much of it old, so perhaps things have moved on ...

Thanks a bunch for any tips.

PeteB