ODBC Connect to Excel

[shane_spencer]

I've just updated to Office 2016 on my new Windows 10 laptop.

A script that was previously working is now failing:

SQL##f - SqlState: IM002, ErrorCode: 0, ErrorMsg: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
ODBC CONNECT32 To [Excel Files;DBQ=C:\Users\SpecialDates.xlsx]

my bit of script is:

ODBC CONNECT32 To [Excel Files;DBQ=$(vFile)];
Sheets_temp:
SQLTABLES;
DISCONNECT
;

Can anyone tell me how to fix this?

[prieper]

hmmm
first would check, whether the Excel-file exists.
Did you try to access this file manually? Probably the file resp directory was not migrated.

[shane_spencer · Accepted solution]

I  realised that my ODBC drivers we no-longer installed. I found the following that advised to install Access Runtime, which fixed the issue:

https://support.office.com/en-us/article/connect-to-an-odbc-source-49b0cf4d-ef78-4ad1-b224-39091c067...

only thing to be careful of is that you can only install 32 or 64 bit, so you need to make sure your user(s) all have the same version installed.