Data format error when trying to load a query from MS Access

Jimmy Dimes

New Member
Joined
Jul 27, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I have queries in a MS access database linked to a MS SQL database ("SSCAPP"). When I try to display the queries in Excel (using get external data from an access database), I can see the queries but when I try to load them I get the following error "[Data format error] ODBC--Connection to 'SSCAPP' failed". I was able to do this in the past, it stopped working after an office 365 update.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Do you have any NZ functions within your query? If so, you will not be able to read the query into Excel as it is not recognized.
 
Upvote 0
Can you post the SQL code of your Access query, and a sample of the data being returned by the query (as long as it doesn't contain any sensitive information)?
 
Upvote 0
SELECT dbo_PurchOrd.PONbr, dbo_PurchOrd.PODate, dbo_PurchOrd.VendID, dbo_Vendor.Name, dbo_PurchOrd.Status, dbo_PurOrdDet.ReqdDate, dbo_PurOrdDet.InvtID, dbo_PurOrdDet.QtyOrd, dbo_PurOrdDet.QtyRcvd, [QtyOrd]-[QtyRcvd] AS [Open QTY], dbo_PurOrdDet.UnitCost, dbo_PurOrdDet.ExtCost, dbo_PurOrdDet.user5 AS Customer, Date() AS [Todays date]
FROM (dbo_PurchOrd LEFT JOIN dbo_PurOrdDet ON dbo_PurchOrd.PONbr = dbo_PurOrdDet.PONbr) INNER JOIN dbo_Vendor ON dbo_PurchOrd.VendID = dbo_Vendor.VendId
WHERE (((dbo_PurchOrd.Status)="o") AND (([QtyOrd]-[QtyRcvd])>0) AND ((dbo_PurOrdDet.PurchaseType)="GI"));

PONbrPODateVendIDNameStatusReqdDateInvtIDQtyOrdQtyRcvdOpen QTYUnitCostExtCostCustomerTodays date
2471206-Nov-18MACS1Gerdau Special SteelO22-Apr-192.250HRRD4150QT410000410000.642526342.5AAGE127-Jul-21
2471206-Nov-18MACS1Gerdau Special SteelO06-May-192.250HRRD4150QT440000440000.642528270AAGE127-Jul-21
2492010-Jan-19TIMK2Timken Steel Corp.O10-Jul-196.0X3.0HRRD4340AQANN500000500000.945000AUTO427-Jul-21
2492110-Jan-19TIMK2Timken Steel Corp.O10-Jul-195.5X4.0HRRD4340AQANN200000200000.918000AUTO427-Jul-21
2558129-Jul-19SPEC4Specialty Metals LLCO29-Jul-197.500RDH131558015581.973069.26FIRT127-Jul-21
2560205-Aug-19MACS1Gerdau Special SteelO05-Nov-191.250CFRDCRMOV280000280000.9375262501STOCK27-Jul-21
2582224-Oct-19SBQS1SBQ STEEL USA LLCO24-Oct-193.375HRRD114435103510.281898.91sage127-Jul-21
2582624-Oct-19MACS1Gerdau Special SteelO31-Dec-191.062CFRD4140R5000050000.80540251STOCK27-Jul-21
2582825-Oct-19MACS1Gerdau Special SteelO02-Jan-201.000CFRD4140RSB130000130000.8410920drgu127-Jul-21
2583225-Oct-19ALTO1Alton Steel, Inc.O16-Dec-191.125HRRD10186500065000.28182027-Jul-21
2583929-Oct-19MACS1Gerdau Special SteelO20-Jan-201.062CFRDCRMOV200000200000.93751875027-Jul-21
 
Upvote 0
OK, I don't see anything that stands out.
However, if the data is in SQL, and you want to view it Excel, why go through Access?
Why not create the query in SQL, and connect Excel directly to it (see here: View SQL Access data in Microsoft Excel).
 
Upvote 0
I do not know how to write SQL queries in Excel. Do you think that would fix the problem. I am thinking its the connection to the SQL database, I am going to create an access database that is not linked to a SQL database and see if I can load the query in Excel
 
Upvote 0
I do not know how to write SQL queries in Excel. Do you think that would fix the problem.
I am not suggesting that. I am suggesting creating the query right in the SQL Server instead (they call them "Views" in SQL).
But the syntax between Access SQL code and SQL Server code is very similiar.
Then you can link that SQL View (query) to Excel.
 
Upvote 0
An alternative to Joe's solution is to Open a blank excel workbook. Click on Data, Get Data, From Database, From Access. Click on your Access Query and bring the data into Power Query Editor. Click on Home and Close and Load.

If you have updates to the Query, then in the Excel file, click on Data, Refresh All and the updated information will populate in the Excel file.
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top