Results from query into excel

PeterBaileyUk
PeterBaileyUk used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
If it is failing where you say it is then you are not successfully accessing the records in the database.  There is possibly an issue with the connection string.  
When you call the .Open strQuery, cnn part this is where it actually does the DB connection to pull the recordset down

Author

Commented:
irrespective of getting past this part:

 Set cnn = New ADODB.Connection
   With cnn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .ConnectionString = "Data Source=" & strPathToDB & ";"
      .Open
   End With
Top Expert 2010

Commented:
For strQuery, instead of passing in the name of your saved query in Access, enter the SQL code for that query: "SELECT ... FROM ... WHERE ..." etc.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
I dont think that will work as the query relates to another query..

SELECT Count(QryCWCodeData.[MVRIS CODE]) AS [Total CW Cars], Count(QryCWCodeData.Abi) AS CountOfAbi, Count(QryCWCodeData.ADL) AS CountOfADL, Count(QryCWCodeData.Cap) AS CountOfCap, Count(QryCWCodeData.DH) AS CountOfDH, Count(QryCWCodeData.Kee) AS CountOfKee, Count(QryCWCodeData.IDS) AS CountOfIDS, Count(QryCWCodeData.Glasses) AS CountOfGlasses, Count(QryCWCodeData.GSF) AS CountOfGSF, Count(QryCWCodeData.Halford) AS CountOfHalford, Count(QryCWCodeData.Mamsoft) AS CountOfMamsoft, Count(QryCWCodeData.Techdoc) AS CountOfTechdoc, Count(QryCWCodeData.Vivid) AS CountOfVivid, Nz([Year],"No Intro") AS Years
FROM QryCWCodeData
GROUP BY Nz([Year],"No Intro");

do you know a different way to get that data into the spreadsheet?

Commented:
If you don't need to do it via VBA you can use the built in data connector tools.  They are really simple

http://www.tek-tips.com/faqs.cfm?fid=1403

Author

Commented:
I tried that and the two relevant queries do not show in the list, maybe this is why my code didnt work, is it possible queries that have count function in wont work?

Author

Commented:
as in id 37997104

Commented:
Sadly you are right in that Queries that contain sub queries do not show up on the import data option.
What might be a solution - though it is a bit long winded approach would be to create a function in VBA within Access.  This function could call the query and you could generate the results to a table in the database.
Then you have a simple Excel Link (using the built in connector tools) to that table.

Author

Commented:
The code I posted does work it appears it depends on the query too, the query above wont connect. I made a table from the data of that query and called it query1 and that wont import either but the underlying query that supplies them will import, that i dont understand.

the following imports:
SELECT SMMT.[MVRIS CODE], SMMT.[VEHICLE CATEGORY CODE], Mid([smmt].[mvris code],3,1)<>"8" AS Expr1, Mid([smmt].[mvris code],3,1)<>"9" AS Expr2, AbiToMvris.[MVRIS CODE] AS Abi, ADLToMvris.MvrisCode AS ADL, [CapToMvris-CW].MvrisCode AS Cap, CW_DHSystemsID.[MVRIS CODE] AS DH, CW_KeeCode.CWCode AS Kee, CW_to_IDS.MvrisCode AS IDS, GlassesToMvris.[MVRIS CODE] AS Glasses, GSFTechDocToMvris.[Mvris Code] AS GSF, HalfordsToMvris.MvrisCode AS Halford, MamsoftToMvris.MvrisCode AS Mamsoft, TechDocToMvris.[Mvris Code] AS Techdoc, VividToMvris.MvrisCode AS Vivid, Year([INTRO DATE]) AS [Year]
FROM (((((((((((SMMT LEFT JOIN AbiToMvris ON SMMT.[MVRIS CODE] = AbiToMvris.[MVRIS CODE]) LEFT JOIN ADLToMvris ON SMMT.[MVRIS CODE] = ADLToMvris.MvrisCode) LEFT JOIN [CapToMvris-CW] ON SMMT.[MVRIS CODE] = [CapToMvris-CW].MvrisCode) LEFT JOIN CW_KeeCode ON SMMT.[MVRIS CODE] = CW_KeeCode.CWCode) LEFT JOIN CW_to_IDS ON SMMT.[MVRIS CODE] = CW_to_IDS.MvrisCode) LEFT JOIN CW_DHSystemsID ON SMMT.[MVRIS CODE] = CW_DHSystemsID.[MVRIS CODE]) LEFT JOIN GlassesToMvris ON SMMT.[MVRIS CODE] = GlassesToMvris.[MVRIS CODE]) LEFT JOIN GSFTechDocToMvris ON SMMT.[MVRIS CODE] = GSFTechDocToMvris.[Mvris Code]) LEFT JOIN HalfordsToMvris ON SMMT.[MVRIS CODE] = HalfordsToMvris.MvrisCode) LEFT JOIN MamsoftToMvris ON SMMT.[MVRIS CODE] = MamsoftToMvris.MvrisCode) LEFT JOIN TechDocToMvris ON SMMT.[MVRIS CODE] = TechDocToMvris.[Mvris Code]) LEFT JOIN VividToMvris ON SMMT.[MVRIS CODE] = VividToMvris.MvrisCode
GROUP BY SMMT.[MVRIS CODE], SMMT.[VEHICLE CATEGORY CODE], Mid([smmt].[mvris code],3,1)<>"8", Mid([smmt].[mvris code],3,1)<>"9", AbiToMvris.[MVRIS CODE], ADLToMvris.MvrisCode, [CapToMvris-CW].MvrisCode, CW_DHSystemsID.[MVRIS CODE], CW_KeeCode.CWCode, CW_to_IDS.MvrisCode, GlassesToMvris.[MVRIS CODE], GSFTechDocToMvris.[Mvris Code], HalfordsToMvris.MvrisCode, MamsoftToMvris.MvrisCode, TechDocToMvris.[Mvris Code], VividToMvris.MvrisCode, Year([INTRO DATE])
HAVING (((SMMT.[VEHICLE CATEGORY CODE])<'c') AND ((Mid([smmt].[mvris code],3,1)<>"8")=False)) OR (((SMMT.[VEHICLE CATEGORY CODE])<'c') AND ((Mid([smmt].[mvris code],3,1)<>"9")=False));

The query at post id ID: 37997104 will not import, any ideas?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial