Unable to link Excel to an Access crosstab query

[bankr]

Hi, I'm having the following problem:

I'm trying to link an Excel spreadsheet to an Access query using the usual method:
Data->Import External Data->New Database query etc.
When I'm in the Choose Columns window of the Query Wizard I try to select the query I want but get the following error message:
"Type mismatch in expression"
The query in question is a crosstab query and runs fine from within Access. If I try to get data from other queries or tables within the same database it works fine.

If I try the new method en Excel 2003
Data->Import External Data->Import Data
the query I want doesn't even appear in the list.

Some relevant information:
Currently I'm using Office 2003 but had the same problem w/ the 2002 edition.
The Crosstab query in question is built on a Union query. In the past I've had similar unresolved problems with Union queries.

Any help would be appreciated...

 

[luceze]

Use ADO instead of MSQuery. I have started using that instead and now prefer it to MSQuery. I can't take credit for the code. It is a Nate Oliver special.

Dim cn As Object, rs As Object
Dim MySql As String, dbfullname As String
Dim myArr() As String, i As Long
dbfullname = "c:\temp\MyDB.mdb"
MySql = "TRANSFORM First([myTable].Names) AS Expr1 " & _
    "SELECT [myTable].RowField " & _
    "FROM [myTable] " & _
    "GROUP BY [myTable].RowField " & _
    "ORDER BY [myTable].RowField " & _
    "PIVOT [myTable].[ColumnField];"
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
       & dbfullname & ";"  'Create DB connection
Set rs = CreateObject("ADODB.Recordset")
With rs
    Set .ActiveConnection = cn
    .Source = MySql 'Pass your SQL
    .Open , , 3, 3
    ReDim myArr(0 To .Fields.Count - 1)
    For i = LBound(myArr) To UBound(myArr)
        myArr(i) = .Fields(i).Name
    Next
    Sheets(1).[a1].Resize(, UBound(myArr) + 1) = myArr
    Sheets(1).[a2].CopyFromRecordset rs
    .Close
End With
cn.Close
Set rs = Nothing: Set cn = Nothing

Just change the path to the database to the location of your database and the SQL string to the SQL behind your crosstab.

HTH,
Eric

 

[bankr]

Thanks, I won’t bother with MS Query again.

 

[HubbleO]

I seem to be resurrecting a long dead thread here but my problem seems identical and the solution isn't working for me.

I was trying to use MSQuery to get data from Access into Excel. The query in question combines a crosstab and a union query. After having no luck there I searched here and found the above code which I modified for my project.

It seems the you just can't get the union query or crosstab into Excel though. This is the SQL I'm using

SELECT StatementDate, A, B, C, D FROM quniStatementDays LEFT JOIN qxtbCashFlowBills ON quniStatementDays.StatementDate = qxtbCashFlowBills.BillDate WHERE StatementDate>Date()-490;

which as you can see contains both a union and crosstab query. The error states it cannot find "quniStatementDays." I modified the SQL and tried a few queries and any union or croasstab query generates the error. If I change it to just a straight select query the data comes into Excel

SELECT * FROM qryLawyersAssigned;

and that works fine.

Any ideas? I know I can work on the Access side and populate a table and bring that into Excel but I'd prefer to have Excel do the work if I can.

Thanks,
Jim