Excel query and parameters

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

Kayda

How about using ADO, building your query in Excel, running (say to a recordset) and then returning the output directly to excel?

Here's some sample generic code that may get you going.

Set cn = CreateObject("adodb.connection")
  Set rs = CreateObject("adodb.recordset")
  
    
  cn.Open "provider=microsoft.jet.oledb.4.0;data source = c:\temp\mydb.mdb"
  cmd.activeconnection = cn
   
  
  rs.Open "select * from mytable where myfield = 'mything' group by myfield", activeconnection:=cn
  Range("a1").CopyFromRecordset rs
    
    
  rs.Close
  Set rs = Nothing
  cn.Close
  Set cn = Nothing

rylo