This is the driver installed via: https://www.microsoft.com/en-us/download/details.aspx?id=54920, Microsoft Access Database Engine 2016 Redistributable. It installs the ACE 12 OLEDB and ODBC drivers so you can (for example) query Excel sheets using SQL into a VFP cursor. I've been using the 2010 version for years. This version isn't playing ball with VFP however. It appears to be installed correctly because I can pull data out of a sheet from a C# program. However trying to do the same from VFP, with the exact same connection string throws up a dialog looking for a data source to specified, and if you cancel that the SQLCONNECT() fails. Here's the connection string:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=excel.xlsx;Extended Properties="Excel 12.0;HDR=YES;" Has anyone used this version successfully from VFP using the OLEDB version?
-- Alan Bourke alanpbourke (at) fastmail (dot) fm
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
On Wed, Feb 7, 2018 at 6:36 AM, Alan Bourke alanpbourke@fastmail.fm wrote:
program. However trying to do the same from VFP, with the exact same connection string throws up a dialog looking for a data source to specified, and if you cancel that the SQLCONNECT() fails. Here's the connection string:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=excel.xlsx;Extended Properties="Excel 12.0;HDR=YES;" Has anyone used this version successfully from VFP using the OLEDB version?
From the Install Instructions on that page:
If you are connecting to Microsoft Office Excel data, add the appropriate Extended Properties of the OLEDB connection string based on the Excel file type:
File Type (extension) Extended Properties --------------------------------------------------------------------------------------------- Excel 97-2003 Workbook (.xls) "Excel 8.0" Excel Workbook (.xlsx) "Excel 12.0 Xml" Excel Macro-enabled workbook (.xlsm) "Excel 12.0 Macro" Excel Non-XML binary workbook (.xlsb) "Excel 12.0"
Perhaps you need to add "Xml" to the extended properties setting?
Thanks Ted, it doesn't make any difference unfortunately.
How 'bout the bitness? I've gotten bitten, more than once, because the 32-bit VFP was using 32-bit ODBC but I used the wrong ODBC Administrator to set up the DSN, or was trying to talk to a 64-bit app on the other end.
On Wed, Feb 7, 2018 at 7:22 AM, Alan Bourke alanpbourke@fastmail.fm wrote:
Thanks Ted, it doesn't make any difference unfortunately.
-- Alan Bourke alanpbourke (at) fastmail (dot) fm
[excessive quoting removed by server]
It's 32-bit Office and the 32-bit drivers. The C# tester which works is compiled to 32-bit too.
https://www.connectionstrings.com/excel/
This gives a lot of the necessary settings for your version of office product as well as intended data you are pulling.
On Wed, Feb 7, 2018 at 7:56 AM, Ted Roche tedroche@gmail.com wrote:
How 'bout the bitness? I've gotten bitten, more than once, because the 32-bit VFP was using 32-bit ODBC but I used the wrong ODBC Administrator to set up the DSN, or was trying to talk to a 64-bit app on the other end.
On Wed, Feb 7, 2018 at 7:22 AM, Alan Bourke alanpbourke@fastmail.fm wrote:
Thanks Ted, it doesn't make any difference unfortunately.
-- Alan Bourke alanpbourke (at) fastmail (dot) fm
[excessive quoting removed by server]
-
Alan Bourke -
Stephen Russell -
Ted Roche