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
Microsoft ExcelMicrosoft Access

Avatar of undefined
Last Comment
PeterBaileyUk

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
wobbled

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
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.
PeterBaileyUk

ASKER
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?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
wobbled

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
PeterBaileyUk

ASKER
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?
PeterBaileyUk

ASKER
as in id 37997104
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
wobbled

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.
PeterBaileyUk

ASKER
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?