Trying to load data from an Access crosstab query, but with variable field names

[baldmosher]

I have a SELECT query in an Access 2010 ACCDB that I'm trying to load into Excel using either Microsoft Query and/or VBA, whilst applying a WHERE filter to the original Access SELECT query.

What complicates matters is that I have to use * to load all the fields from a crosstab sub-query, because it could be any of 400+ fields in use in the query, and I can't predict when a new field will be added into the data.

So I'm basically trying to load this into an Excel querytable or pivot (either would do but prefer querytable):
SELECT * FROM [qryCrosstab] WHERE [Field1]='ABC'

However, I don't know how to do this.
- Microsoft Query apparently won't let me use * and times out when I try to use it, so that's no good.
- Excel won't let me apply a filter to the Access linked query properties (or will it?) unless I create multiple copies of the same query in the source database with each filtered item (and I'm trying to avoid doing this).
- VBA queries are slightly alien to me, unless I am using the MS Query interface, doing the VBA within Access, or amending someone else's code.

Is this as simple as I think it should be?

Or is it so difficult that I should just deal with the extra workload and create multiple copies of the same query in Access with each [Field1] filter item applied? We're only talking about 10 copies, so not the end of the world to do that.

I always figure there must be an easy solution!!

 

[ranman256]

you want COPYFROMRECORDSET.

It would be easier to export the crosstab FROM access. But if you cant, then on the Excel side, you must connect to the db using ADO:

Public Sub CopyRST()
Dim con as ADODB.Connection
Dim rs as ADODB.Recordset    
 uid = "BSmith"
 pwd = ""
 DB = "\\amer\UMA\104J.mdb"
 vProvid = "Microsoft.Jet.OLEDB.4.0"     '  "SQLOLEDB"
   
With con
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Properties("User ID").Value = uid
    .Properties("Password").Value = pwd
    
    .Open "Data Source=" & DB & ";Jet OLEDB"
End With
 
Set rs = con.Execute("qsNames_simps")
ActiveWorkbook.Worksheets("Sheet1").Range("A1").CopyFromRecordset rs


rs.Close
con.Close
End Sub

 

[baldmosher]

I think I may need to do more research on OLEDB, as I can't see where in that code you would specify the filter? What is "qsNames_simps"?

By export it, do you mean create several new tables with the relevant filters, and import from each table? I guess that's the easiest way to get around the problem. It's currently taking 15+mins just to create each copy of the query....!!

It would be so much easier if I could specify the fields, but that just isn't practical, without a heck of a lot of coding and workarounds. That's certainly possible to do (create a SELECT that outputs the full list of fields, import that list of fields into Excel, then call each field in turn to create a code string for DoCmd.RunSQL or similar)

 

[ranman256]

qsNames_simps is my query holding the data.
You could also assign sql text here.