Excel – Error when refreshing data from external source in excel. Data source name not found and no default driver specified

microsoft excelmicrosoft-excel-2007odbc

I have an excel workbook which has an external data source to create a pivot table. This file was sent to me. I can open it up correctly, but when I try to refresh the data I get the following error:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

As far as I'm concerned, Windows should use the default ODBC driver (odbcad.exe) for the connection. Any idea what might be causing the error when refreshing the data?

EDIT:
The connection string has DSN, UID, TRUSTED_CONNECTION, APP, WSID and DATABASE fields.

EDIT: I set up the ODBC connection, but now I'm getting the following error:

[ODBC Driver Manager] The Specified DSN contains an architecture mismatch between the Driver and Application

Best Answer

Even thought the ODBC connection information is present in the Excel workbook, an ODBC connection must be established between your PC and the data source in order for the workbook to connect.

Not sure what OS you are using, but here is Using the ODBC Data Source Administrator for Windows 7.

If you do not know the information to setup the connection, contact your network administrator.

Related Solutions

Windows – ODBC Data Source Driver ‘Does Not Exist’ After Updating to Windows 10

It is likely that I uninstalled the MS database drivers when I uninstalled the older version of Office (and not related to the recent Windows update) and the new one must not come with the drivers as standard.

The drivers I needed were within the 'Microsoft Access Database Engine 2010 Redistributable' that is currently available at the following address:

https://www.microsoft.com/en-us/download/details.aspx?id=13255

Excel – VBA Automation error when querying database through ODBC

The problem is your connection string. Note that you mentioned UID, password, etc. information in your question, but this info appears nowhere in your code. To connect to the database, you must supply this information in the connection string.

Try the following:

Connection.ConnectionString = "DSN=sgdv;UID=l8qc1;PWD=l8qc1;"
Connection.Open

I can't guarantee this will work, but it should at least get you on the right track. If it doesn't work, you may need to tweak the connection string. For more information about ADODB connections and connection strings, see this MSDN page:

https://msdn.microsoft.com/en-us/library/ms807027.aspx

Best Answer

Related Solutions

Windows – ODBC Data Source Driver ‘Does Not Exist’ After Updating to Windows 10

Excel – VBA Automation error when querying database through ODBC

Related Question