Microsoft.ACE.OLEDB connection for excel ? 64 vs 32 bit.

[Damon 0]

My goal is to simply open a basic excel sheet with a few columns, (ID, Name, Color) and then load it to a Tstringgrid so i can run some validation on it and import it (or update records) in a MySql db.

My issue is I cannot seem to get a connection and it is due to the provider being 64 bit and the delphi app being 32 bit.

LAdoQueryExcel.ConnectionString:= 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source='+opendialog1.FileName+';Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1"';
        LAdoQueryExcel.SQL.Add('SELECT * FROM [Sheet1$]');
        LAdoQueryExcel.Open;

No matter what, i get "provider not found".

I tried to install the 32 bit access runtime but it will not let me unless i uninstall office entirely.  There must be a way around this.  I can't control if people have 32 or 64 bit office installed.

TIA for your input.  If there's a different approach all together, then by all means - bring it.  I'm not married to the ADO connection it was just what i thought was the simplest way to grab from an excel sheet.

[Lajos Juhász 238]

Another options is to use FireDAC, Ole Automation or library to read xlsx files directly for example TMS Flexcel.

[obmsch 0]

I don't know up to which version this works, but at least the Microsoft Access Database Engine 2010 Redistributable
should, if you pass the '/passive' and maybe '/s' (for silence).

• To install the Microsoft ACE OLEDB Provider 32-bit on a machine running Office 64-bit:   AccessDatabaseEngine.exe /passive
• To install the Microsoft ACE OLEDB Provider 64-bit on a machine running Office 32-bit:   AccessDatabaseEngine_X64.exe /passive

Hope that helps,

Martin

[Virgo 16]

10 hours ago, obmsch said:

• To install the Microsoft ACE OLEDB Provider 32-bit on a machine running Office 64-bit:   AccessDatabaseEngine.exe /passive

I need to remember this.

We ever needed to only read from excel and whole sheet at the time. So we created extra 64 bit executable, that communicates with main executable via named pipe and reads sheet to json and sends it back. Where it is loaded to in-memory dataset. It is used as a fallback, when just reading table with ADO fails with 'Provider cannot be found' error. And when that also fails, then and error is shown.