Fix Excel DataFormat.Error When Importing an Access Query from SQL Server
Question details
The user needs to import an Access query that combines local Access data with linked SQL Server tables into Excel.

- Product
- Microsoft Excel, Microsoft Access, and SQL Server
- Device & OS
- not provided
- Scenario
- Excel imports an Access query whose linked SQL Server tables use an ODBC data source.
- Observed behavior
- Excel reports “DataFormat.Error: ODBC connection failed” during the import.
Identify the Access query, its linked SQL Server tables, and the DSN used by those links. Confirm which account and ODBC driver are used to connect to SQL Server.
Test the Access links and ODBC connection
Use this approach to determine whether the failure occurs before Excel imports the query.
Open the database in Access and double-click each linked SQL Server table in the navigation pane. Confirm that its records load without a connection or permission error.
Open ODBC Data Sources and inspect the DSN used by the linked tables. Verify the server, driver, and authentication settings, and use the driver's connection test if available.
Connect to SQL Server using the same account outside Excel and confirm that it can read the tables used by the Access query. Check that the ODBC driver and DSN are available to the application using them.
In Access, double-click the query and confirm that it returns results. Then return to Excel and select Data > Refresh All to retry the import.

Initialize linked tables in Access before importing
Try this if the Access application creates or updates its SQL Server links when it starts.
Connect Excel directly to SQL Server
Use a direct SQL Server connection to avoid the Access link when the required data is available on the server.
Work with imported spreadsheet data in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files and continue working with imported data as you move between office suites. It does not repair Excel's ODBC connection.

Frequently Asked Questions
How can I tell whether the SQL Server link in Access is working?
Open the Access database and double-click the linked table in the navigation pane. If its records load, run the Access query as well to check the complete data path.
Why might opening Access before Excel help?
Some Access applications create or update linked tables in a startup macro or application code. Opening the database normally can initialize those links before you retry the import in Excel.
Can Excel import the data directly from SQL Server instead?
Yes, if the required data is available on SQL Server. In Excel, select Data > Get Data > From Database > From SQL Server Database and connect to a suitable table or view.
What if the Access query combines local and SQL Server tables?
A SQL Server connection cannot directly query local Access tables. Make the local data available to SQL Server or combine it separately, and adapt the Access SQL if you create a server-side query.
Where do I check the ODBC DSN?
Open ODBC Data Sources and inspect the DSN used by the Access links. Check the server, driver, and authentication settings, and use the driver's connection test if available.




