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!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
Welcome to the Board!

Do you have better luck if you export the data to Excel instead of trying to link it?
 

canlynk

New Member
Joined
Jun 24, 2011
Messages
3
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.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,553
Office Version
2013
Platform
Windows
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/Microsoft-Office-Excel-2007/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.

ξ
 

canlynk

New Member
Joined
Jun 24, 2011
Messages
3
Using the new database query option worked! Thank you so much for your help!
 

CastroTheCat

New Member
Joined
Aug 11, 2015
Messages
21
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.
 

BlueCheetah

New Member
Joined
Dec 11, 2019
Messages
2
Office Version
2013
Platform
Windows
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...
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,553
Office Version
2013
Platform
Windows
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.
 

BlueCheetah

New Member
Joined
Dec 11, 2019
Messages
2
Office Version
2013
Platform
Windows
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...
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,553
Office Version
2013
Platform
Windows
Well as I said before please provide some sample data that illustrates the problem.
 

Forum statistics

Threads
1,082,306
Messages
5,364,410
Members
400,801
Latest member
julievandermeulen

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top