Linking MS Excel to MS Access queries

Question

I have an Access database that I use to to run a bunch of aggregate TOP 5 queries. The end game of this particular function is ppt presentations with charts. So to automate this a bit, I created an excel spreadsheet with a bunch of pre-made charts in it. The I linked/data imported the query results into the excel spreadsheet, and set the cell ranges to match the source ranges for the charts. Finally I set the spreadsheet to enable auto refresh upon every "file open", and presto, I run the queries in access with a button press, then open the excel file, and the data refreshes, and they are my charts. I then use some VB to copy paste all active charts into ppt, but that is another matter.

Okay so here is the snag I hit. I needed to do the exact same procedure with another spreadsheet from another ag query. Before the process I was using was just clicking data > import external data > import data and then choosing the file of the database, and then it would populate a list of tables and queries to choose from. Only now, it will not show any queries in that populated list; only tables!?!

any ideas? btw MSO 2003

Answer 1

I presume this is a security feature that has been introduced?

Try selecting any table then select Data>Import External Data>Edit Query

Then select SQL as the command type and either enter the query SQL or just select from a query i.e.:

select * from qryCat;

Hope this helps thanks Mark

Answer 2

I can't reproduce that Justin. In the dialog that shows the tables, scroll over to the right and check the TYPE column. Queries will have a type of VIEW and tables of TABLE. Verify that they all say TABLE.

Next, try using Data - Import External Data - New Database Query instead of 'Import Data'. Select the Access driver and the Access mdb. This will bring up a different dialog, but should show both tables and queries. See if that shows only tables or shows both.

Answer 3

Thanks very much! That Solved the problem!!