troubleshooting Question

Results from query into excel

Avatar of PeterBaileyUk
PeterBaileyUk asked on
Microsoft AccessMicrosoft Excel
9 Comments1 Solution194 ViewsLast Modified:
I have a worksheet in excel and want to import data from a query into it but not sure how to get the recordset part working, the connection has been made succesfully using code from EE.

Sub GetAccessData()
  Dim cnn As ADODB.Connection
   Dim strQuery As String
   Dim rst As New ADODB.Recordset
   Dim strPathToDB As String
   Dim wks As Worksheet
   Dim i As Long
   
   Set wks = Sheets("CW Data")
   
   ' change database path and query name as required
   strPathToDB = "N:\data\SimpleStats2.mdb"
   strQuery = "QryTotalCWCarsYearsBreakdown"
   
   Set cnn = New ADODB.Connection
   With cnn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .ConnectionString = "Data Source=" & strPathToDB & ";"
      .Open
   End With



   ' create new recordset
   Set rst = CreateObject("ADODB.Recordset")
   
   ' open recordset using query string and connection
   With rst
'FAILS HERE
      .Open strQuery, cnn
      ' check for records returned
      If Not (.EOF And .BOF) Then
         'Populate field names
         For i = 1 To .Fields.Count
            wks.Cells(1, i) = .Fields(i - 1).Name
         Next i
         ' Copy data starting from A2
         wks.Cells(2, 1).CopyFromRecordset rst
      End If
      .Close
   End With

help would be much appreciated.





 
   cnn.Close
   Set cnn = Nothing
End Sub
ASKER CERTIFIED SOLUTION
wobbled

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros