query

[efeedback]

Have tables and queries set up in access. Trying to get excel query to look at and retrieve data for analysis. Can view the access database file but query cannot see any tables or queries in the file.

Any ideas??

 

[DonkeyOte]

Post your Excel VB so we can see the query you're running...also list the names of the fields and tables in your Access db.

Luke

 

[Corticus]

Hi,

You should be able to go Data|Get External Data|Run|New Database query from withing Excel.

Select MSAccess database as your data type, and then MSQuery should come up so you can define your query.

HTH,
Corticus

 

[efeedback]

In response to corticus.

I have carried out those steps. Excel identifies the acccess database but returns an error saying "the data source contains no visible tables".

 

[Corticus]

Hmmmm...

That's strange. If Excel can't recognize a database file as a database, that's odd. When looking at an Excel file for data, Access will return a similar error if the data isn't any a standard data-set form with field names across the top and recordsgoing down. I don't see how a data table built in a database would not be recognized as data.
All I can think, try compact and repair, and try exporting the table(s) to a new db, maybe they're currupted or something

Sorry I couldn't be more help,

 

[Anne Troy]

efeed:

Are the tables visible when you open the database directly? Make sure you're NOT viewing hidden objects first to make sure. You may need to unhide them first, no? I didn't test it.

 

[dk]

If you're seeing the error 'contains no visible tables' then try this. From the Excel Query Wizard choose Options and then make sure the System Tables checkbox is ticked. Does that work?

 

[efeedback]

Got it, don't know what was wrong with db but importing it into another has made it all visible.

Thanks for your help.

 

[efeedback]

Now that I can write queries and retrieve data, I wonder if it is possible to do something else.

Rather than fill up an excel workbook with sheets containing the results of lots of queries, can you simply refer to the query in a formula leaving the actual data in access?? Alternatively, can you write a query in access that the formula can see??

I would like ot be able to write a report in excel with all the necessary formulas, those formulas containing queries that read the data in the access database. This way, whenever i open the excel workbook I thought it would collect the latest data from the access database.

 

[Anne Troy]

e:

Why are you using Excel at all? Is it a requirement?

You can write a formula ONCE in an Access query to get your results, where in Excel, you must write that formula for each line. Are you aware of that?

Then, you could automate (using Access's built in macro features) to export it to an Excel file. You could even make that macro run when you open the database or close the database so that the Excel is updated (overwritten) regularly.