ADO from Excel to retrieve saved Access query 1

[Phantek]

I am trying to retrieve the information from a saved Access query and display the results in Microsoft Excel. I managed to find some helpful code, but it is only working for some queries and not others. If I run it on certain queries, it gives me the full results. If I try it on others, it does not find any records. Any idea what could be causing this?

Sub GetQuery()

    Dim wsl As Worksheet
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim r As Integer, i As Integer
    Dim fld As ADODB.Field
    
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Set rs2 = New ADODB.Recordset

    Set wsl = Sheet3

    With cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open "C:\Test.mdb"
    End With
        
    
    With rs
        .Open Source:="qryClone", ActiveConnection:=cn, CursorType:=adOpenForwardOnly, LockType:=adLockReadOnly

        r = 2

        Do While Not .EOF
            i = 1
            For Each fld In .Fields
                wsl.Cells(r, i).Value = fld.Value
                i = i + 1
            Next
            r = r + 1
            .MoveNext
        Loop
    
    
    End With
    
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

End Sub

 

[PHV]

The saved query shouldn't have any parameter, I guess.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Phantek]

Yes, valid point. The query in this case does not require any new parameters - it has everyting that it needs.

 

[PHV]

What is the SQL code of this query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

BTW, did you try to use MS-Query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Phantek]

SELECT Leave.ID, Leave.Area, Leave.Location, Leave.Department, Leave.EType, Leave.LocNum, Leave.LeaveRsn
FROM Leave
WHERE (((Leave.LeaveRsn) Is Null));

I just tried, and I am able to get the field names, but not any record values...

 

[PHV]

but not any record values
Perhaps you have no record with null LeaveRsn ...
What about this SQL instead ?

SELECT Leave.ID, Leave.Area, Leave.Location, Leave.Department, Leave.EType, Leave.LocNum, Leave.LeaveRsn
FROM Leave
WHERE Trim(Leave.LeaveRsn & '') = ''

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Phantek]

I just realized, it's a query of a query of a query. I thought that could be part of the problem, but I have other queries that work when I retrieve from a query of a query.

I tried running the prior query ("Leave") and I had the same problem. It pulled from query "Everyone". I was able to grab the records from "Everyone" without an issue. Maybe the problem is then with the following query, "Leave".

SELECT Everyone.ID, Everyone.Area, Everyone.Location, Everyone.Department, Everyone.EType, Everyone.LocNum, LeaveCodes.LeaveRsn
FROM LeaveCodes RIGHT JOIN Everyone ON LeaveCodes.Leave_Description = Everyone.Leave_Description
WHERE (((Everyone.Status) Like "*Benefit*"));

 

[PHV]

As you use ADO, I'd try to replace this:
Like "*Benefit*"
with this:
Like '%Benefit%'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Phantek]

You solved it PHV, thank you so much!

Except this is kinda crazy. Now that I have changed the query to Like '%Benefits%', that query ONLY works via ADO - it I look at it in Access it does not work. But that is alright, I will create a copy of the query that will only be accessed via ADO.

Thanks again!