MS Query

[dhstein]

The situation is as follows:

We have an Access 2007 database. We have an Excel 2007 file which can
connect to that database. One of the queries in the Access database is
somewhat complex (GROUP BY clauses, function calls, etc.), but also very
useful. Although they have the ability to execute the query in Access and
export it to Excel, it would be helpful to run that query in the Excel file
using MS query. But I think that MS Query is somewhat basic and will only
execute "simple" queries. If I connect to the external source - then I only
have access to tables - not queries. So the question is, is there a way to
run the SQL statement from Excel or can you suggest some way to do this?
Thanks for any help on this.

 

[Ken Snell [MVP]]

Why the desire to run the query from EXCEL? Tell us more about the "why" and
we likely can suggest ways for the "How".

 

[dhstein]

Ken thanks for your response. The reason is that the user who is paying for
this likes Excel. He wants to get this data and manipulate it in Excel. He
doesn't want to go into Access first and export the query into Excel. So I
want to set up an Excel macro for him to push a button and pull in the data
from the query. Then he'll be happy - and my goal is to make him happy.

 

[Douglas J. Steele]

You say that your query has function calls in it. Assuming you mean
user-written VBA functions, you won't be able to run the queries from Excel.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

 

[Ken Snell [MVP]]

I concur with Douglas. Depending upon the SQL statement of the query that
you need to run, trying to run it by automating ACCESS from EXCEL may not
work.

However, another idea would be to use a VBA macro in EXCEL to open an ACCESS
database "under the covers", and have an AutoExec macro in that ACCESS
database run the query and export its results to an EXCEL workbook, and then
close the ACCESS database. It could not be the same workbook file that runs
the VBA macro, as ACCESS cannot export to a file that has been opened by
another user.

Or you could give the user a shortcut icon that would open the ACCESS
database, do the export, and close the database. Is either of these options
a possible alternative?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/