Database query and parameters

[Kayda]

Hello:

It seems to me that the following things are true:

1. You cannot base and Excel query (using "Microsoft Query" at Data |
Import External Data | New Database Query) off of a Microsoft Access
query, if the Microsoft Access query has parameters.
2. You cannot use both GROUP BY and a parameter in an Excel query. If
you use "GROUP BY", then it tells you the query is too complex to be
displayed in design view. After this, if you try to add a parameter to
the query it tells you that you cannot add a parameter if the query
cannot be displayed in design view.

So, my question is, how do I query my Access database using a)
parameters and b) GROUP BY at the same time? I could run the query in
Access, then export to Excel, but that won't work because I need the
query embedded in a formatted Excel sheet.

Thanks,
Kayda

 

[AnandaSim]

Hello:

It seems to me that the following things are true:

1. You cannot base and Excel query (using "Microsoft Query" at Data |
Import External Data | New Database Query) off of a Microsoft Access
query, if the Microsoft Access query has parameters.

The bridge between Excel and Access databases can be DAO or ADO
recordsets. If you know VBA + DAO or VBA + ADO programming, you can
instantiate a recordset from an SQL string or from a parameterised
query. And then you can place the results of the recordset onto cells
in Excel easily using the CopyFromRecordSet command in VBA.