Data From Access Not Displaying Properly in Excel

canlynk

New Member
Joined
Jun 24, 2011
Messages
3
I am attempting to link to and display the results of an Access select query in Excel using the Excel Data menu "From Access" option. There are three columns in the Access select query, two text format and one number format. The number format column displays in Excel, the text colums do not. I have tried adjusting the cell formats and the connection properties to no avail. Any help would be appreciated.

Thanks in advance!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Welcome to the Board!

Do you have better luck if you export the data to Excel instead of trying to link it?
 
Upvote 0
Technically yes i could (Or even copy the data straight from the select query and paste to the spreadsheet), but this spreadsheet is a report based off that data for end users who will not take those extra steps.

I need the data to be lynked to the Access database so that I can set it to refresh automatically when the user opens the spreadsheet.
 
Upvote 0
It sounds rather unusual, but from my very limited exposure to questions about the "From Access" button, it seems to work best on Access tables. You could try to get a new "database query" going instead, using a data connection. This seems to be a decent explanation:
http://www.java2s.com/Tutorial/Micr.../0260__Collaboration/CreateaDatabaseQuery.htm

They show you how to save the query at the end, but I think if you don't take that step it will be saved "inside" the workbook. An external query file is useful if you want to re-use or share the query definition.

I would be interested to know what the query is and what kind of data it is pulling - maybe there's a clue in that somewhere.

ξ
 
Upvote 0
It sounds rather unusual, but from my very limited exposure to questions about the "From Access" button, it seems to work best on Access tables. You could try to get a new "database query" going instead, using a data connection. This seems to be a decent explanation:
Create a Database Query : Database Query « Collaboration « Microsoft Office Excel 2007 Tutorial

ξ

This method seems to work really well, and depending on the query might be the best one.

It might be simpler in some cases to edit the original Access query. Excel doesn't seem to work well with queries that have 'vague' parameters such as "Like" statements. If you can change these to definite statements ('CastroTheCat' rather than 'Like "Castro*"'), this seems to work.

Similarly, I've never found a way to get Excel to pick up data from queries that require user input (Start and End dates, for example), although I'm wondering if xenou's method might hold the key to some of these.
 
Upvote 0
I have created 100 of spreadsheets using linked Access queries and tables and I just one that seems not to work properly as mentioned in this ticket. After running some test I just notice that "Like" doesn't work but if you enter "Not Like" it works...??? Not sure why Excel vs Access does this but didn't happen to me before. Can this be related to a Microsoft suite upgrade? Not sure, but frustrating...
 
Upvote 0
I can't think of any particular reason like would not work when not like does. You might need to post some more details on the specific problem you are encountering, with a little bit of sample data or an example of what isn't working.
 
Upvote 0
I have two queries, Qry1 and Qry2 both using a single table, TblA from which I bring all fields by using the * for the queries. So,

Qry1 has all fields from TblA with criteria 'Like "ABC*' on field Clm1
Qry2 has all fields from TblA with criteria 'Not Like "ABC*' on field Clm1 and '"XYZ"' on field Clm2

Qry1 show nothing on Excel but it runs fine in Access
Qry2 runs fine on both

If a do a 'make table' query and the relink in Excel to it, works fine, it is only when linking directly to that particular Qry1...
 
Upvote 0
Well as I said before please provide some sample data that illustrates the problem.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

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