Link to home
Start Free TrialLog in
Avatar of PeterBaileyUk
PeterBaileyUk

asked on

Results from query into excel

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
Avatar of wobbled
wobbled
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of PeterBaileyUk
PeterBaileyUk

ASKER

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
Avatar of Patrick Matthews
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.
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?
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
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?
as in id 37997104
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.
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?