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';
cW1 = ' WHERE Fakturert IS NULL AND Lagt IS NOT NULL';
cQ = cS1 + cF1 + cW1;
..............
AdoConnection1.Connected:=False;
ADOConnection1.Provider := 'Microsoft.ACE.OLEDB.12.0';
ADOConnection1.ConnectionString := 'Data Source=C:\......\#Drift_2008.xls;Extended Properties=Excel 8.0;';
AdoConnection1.Open;
AdoDataset1.Close;
AdoDataset1.CommandText := cQ;
AdoDataset1.Open;

Greatful for possible ideas.

regards Sven

.

Relevant Pages

  • Re: Is ODBC right for me?
    ... I think the new ACCDB ... format utilises the new JET engine (and JET engine was supposed to be in ... I think the major bottleneck in most database application is network ...
    (microsoft.public.sqlserver.odbc)
  • Re: Is ODBC right for me?
    ... I think the new ACCDB ... format utilises the new JET engine (and JET engine was supposed to be in ... I think the major bottleneck in most database application is network ...
    (microsoft.public.sqlserver.odbc)
  • Re: Sir Frank Whittle
    ... > last-ditch expedient by an air force facing imminent defeat; the Meteor ... Prototype Jumo 004A jet engine of the Me 262 used high nickel chrom ... Ministerium) or german air ministry technocrat Helmuth Schelp mapped ...
    (rec.aviation.military)
  • Scramjet To Be Tested
    ... DAYTON - The Pentagon has approved flight testing for a revolutionary jet engine ... five to eight unmanned X-51As at speeds up to seven times the speed of sound, ...
    (rec.aviation.military)
  • Re: Theres a new Borland Delphi directions survey up
    ... >> There is an alternative to the Jet Engine when using Access and its ... And that engine is MSDE. ... applied to the MSDE engine (which certainly is could be seen as SQL Server ...
    (borland.public.delphi.non-technical)