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

J

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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
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
Jimmy Dimes said:
I do not know how to write SQL queries in Excel. Do you think that would fix the problem.
Click to expand...
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
You must log in or register to reply here.

Similar threads

L
  • Solved
Querying access db from excel - 32 bit to 64 bit conversion
Replies
2
Views
567
JEC
JEC
K
  • Question
Import Data from Multiple Web Pages
Replies
0
Views
109
Ka001
K
K
  • Question
Get Data form closed Excel workbook with ADODB
Replies
2
Views
252
Kappes105
K
A
  • Question
Take screenshots & simultaneously capture the link, place it into an Excel file which is linked to a FileMaker/Claris db.
Replies
3
Views
262
Dan_W
D
R
  • Question
VBA to add to data model when creating Power Query tables`
Replies
3
Views
367
Raddle
R

Forum statistics

Threads
1,221,310
Messages
6,159,176
Members
451,543
Latest member
cesymcox

Share this page

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