Another ACE in the hole

[stanl]

I wrote several decent scripts circa 2006-20011 which generously used ADO and Provider=Microsoft.ACE.OLEDB.12.0. Out of caution the scripts were semi-data driven using either a fabricated recordset or Access table to hold connection strings, queries and even templates stored as binary fields [and thank the God of WB for allowing you to execute code from a table field].


Anyway... long story short, I found ADO super fast executing queries like


INSERT INTO [state] SELECT * FROM [state$] IN "" [Excel 8.0;DATABASE=%cXLS%]


which worked with .xls 32-bit files... then moved to Excel 12.0 for .xlsx and Office 2010-2013.  All worked fine by updating the tables w/out updating the compiled exe.  Then I am made aware of a user upgrading to 64-bit and Office 365.


This meant Provider=Microsoft.ACE.OLEDB.16.0 update [based on previous code in post I made about detecting which ACE Provider to use].


but INSERT INTO [state] SELECT * FROM [state$] IN "" [Excel 16.0;DATABASE=%cXLS%]  <=== that is the current version of Excel installed.... failed with 'cannot find installable ISAM"....  reverted to Excel 12.0 and the script worked again.


If you have read this far and think this is an issue with WB: it's not. The fact that WB works well as data-driven is an asset.