Data From Access Not Displaying Properly in Excel

[canlynk]

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]

Welcome to the Board!

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

 

[canlynk]

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]

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.

ξ

 

[canlynk]

Using the new database query option worked! Thank you so much for your help!

 

[CastroTheCat]

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]

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]

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]

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]

Well as I said before please provide some sample data that illustrates the problem.