Re: ADO with ACCESS 2007 and EXCEL 2007?


"Chris.Cheney" wrote
..........................
For access (mdb and accdb) I have had no problems.

But with Excel I have run into a funny situation:

My SQL statement: "SELECT Fieldnames FROM Tablename WHERE
Conditions" works fine if I use the Jet engine with ISAM format 8
(.xls) or the ACE engine with ISAM format 12 (.xlsx).

But if I try the ACE engine with ISAM format 8 (.xls) then I get an
error from ADODataset1.Open to the effect that table "Tablename"
could not be found. (ADOConnection1.Open seems to having worked OK
also in that case)

Any suggestions?

I have had no problems in testing ACE against Excel 8.0 xls files.

I suggest you post the connection string and the actual SELECT.

Chris

I have two identical Excel workbooks, one ".xls" and one ".xlsx". The
code below fails but works OK with Jet as "provider".

From my .pas file: (The dataset name in the connection string is
actually OpenDialog1.Filename, the essential parts of which are shown
below)

const
cS1 = 'SELECT KNr, Lagt, Fakturert, Serie, Antall, Mapper, Salg,
Tekst, Kunde';
cF1 = ' FROM Fakturering';

FROM [Fakturering$]

Fakturering is the name of the work***, is it not? The $ is needed in
both ACE and JET, and the [ and ] are needed because of the $.

It is indeed. I tried to change the select statement as you suggest, but that resulted in EOleexception with message:
''Fakturering$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long'

Also note that multiple simultaneous use of the workbook opened this way
is not possible (i.e. only one ADO connection at a time can be made to
the workbook).

Not a problem.

HTH

Chris

Sorry, NO

BTW: When I try ACE vith the format 8 version of my workbook the exception is EOleexception with message:
'The Microsoft Access database engine could not find the object 'Fakturering'. Make sure the object exists and that you spell its name and the path name correctly'.

regards Sven

.