Phantek
12-11-2012, 12:17 PM
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
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