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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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
Trouble running an access query in excel vba
Replies
2
Views
155
Legal Tender
L
L
  • Solved
Querying access db from excel - 32 bit to 64 bit conversion
Replies
2
Views
723
JEC
JEC
R
  • Solved
VBA Queries refresh and Save data as TXT
Replies
2
Views
159
Artik
Artik
K
  • Question Question
Run-time error '-2147217900 (80040e14)': Syntax error or access violation
Replies
0
Views
94
kristianb63
K
I
  • Question Question
Unable to Open or Copy Corrupted Excel Files on Network Drive
Replies
1
Views
158
Candyman8019
C

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

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