Run a parameter Access 2003 Query from Excel

[puforee]

I have a DB that produces metrics for my group. The output is grouped by date. The collected metrics also contain a field called "Complete"..this could be Yes or No. I found some useful code to use in Excel to do this.
Sub Run_Access_Qry_Test()


'Step 1: Declare your variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer

'Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase _
("\\nw\data\Maint_Train\MT Shared\John S\Bob K Project\Fleet Support\Fleet Support.mdb")
Set MyQueryDef = MyDatabase.QueryDefs("qry_Totals_Split_Non_ECM")

'Step 3: Define the Parameters
With MyQueryDef
.Parameters("[Enter:Yes, No, or Leave Blank]") = Range("D3").Value
End With

'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset

'Step 5: Clear previous contents
Sheets("Test").Select
ActiveSheet.Range("A6:K10000").ClearContents

'Step 6: Copy the recordset to Excel
ActiveSheet.Range("A7").CopyFromRecordset MyRecordset2

'Step 7: Add column heading names to the spreadsheet
For i = 1 To MyRecordset2.Fields.Count
ActiveSheet.Cells(6, i).Value = MyRecordset2.Fields(i - 1).Name
Next i

MsgBox "Your Query has been Run"


End Sub

This works...however. This parameter query only prepares the date for the final output query that groups the data by month. The output query gets its data from the parameter query. How can I run the output query right after the parameter query,so after step 4 above I need to run the output query ("\\nw\data\Maint_Train\MT Shared\John S\Bob K Project\Fleet Support\Fleet Support.mdb")
Set MyQueryDef2 = MyDatabase.QueryDefs("qry_Totals_Non_ECM Totals") returningthe results based on the parameter query?

Sorry if this is confusing but I need you to ask questions so I can get to the root of the issue.

Thanks in advance.

 

[dhookom]

I'm not sure how you query could have run since you DIM MyRecordset but use MyRecordset2.

I hate working with parameters and querydefs. Consider code like the following that is based on the Northwind.mdb file

Sub Run_Access_Query_Test()
    Dim strSQL As String
[COLOR=#4E9A06]    ' Your actual SQL goes in the next lines[/color]
    strSQL = "SELECT Orders.CustomerID, Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShipName, " & _
            "[Order Details].ProductID, Products.ProductName, [Order Details].UnitPrice, [Order Details].Quantity " & _
            "FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON " & _
            "Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID " & _
            "WHERE Orders.CustomerID='" & Range("D3") & "'"

[COLOR=#4E9A06]    'Step 1: Declare your variables[/color]
     Dim MyDatabase As DAO.Database
[COLOR=#4E9A06]     'Dim MyQueryDef As DAO.QueryDef[/color]
     Dim MyRecordset As DAO.Recordset
     Dim i As Integer
     Dim strMDBFile As String
     strMDBFile = "\\nw\data\Maint_Train\MT Shared\John S\Bob K Project\Fleet Support\Fleet Support.mdb"
     strMDBFile = "C:\temp\Access\Northwind.mdb"
     
    
[COLOR=#4E9A06]     'Step 2: Identify the database and query[/color]     
     Set MyDatabase = DBEngine.OpenDatabase(strMDBFile)
[COLOR=#4E9A06]     'Set MyQueryDef = MyDatabase.QueryDefs("qry_Totals_Split_Non_ECM")[/color]
    
[COLOR=#4E9A06]     'Step 3: Define the Parameters
     'With MyQueryDef
     '.Parameters("[Enter:Yes, No, or Leave Blank]") = Range("D3").Value
     'End With
    
     'Step 4: Open the query[/color]     
     Set MyRecordset = MyDatabase.OpenRecordset(strSQL)
     
[COLOR=#4E9A06]    'Step 5: Clear previous contents[/color]
     Sheets("Test").Select
     ActiveSheet.Range("A6:K10000").ClearContents
    
[COLOR=#4E9A06]     'Step 6: Copy the recordset to Excel[/color]
     ActiveSheet.Range("A7").CopyFromRecordset MyRecordset
    
[COLOR=#4E9A06]     'Step 7: Add column heading names to the spreadsheet[/color]
     For i = 1 To MyRecordset.Fields.Count
         ActiveSheet.Cells(6, i).Value = MyRecordset.Fields(i - 1).Name
     Next i
    
     MsgBox "Your Query has been Run"

End Sub

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

It is possible to run queries in Excel that connect to MS Access tables. These include parameter queries.

You will need to COPY the SQL from your Access query and paste it into MS Query in Excel, after having connected to the appropriate Access db.

 

[puforee]

OK...here is the complete story. I need to pass parameters from excel into my MS Access query...I will call this "Setup Query" The parameters will be for the Completed field and will be Yes or No or blank:

 AS Hours, qry_All.[Hrs to Complete] AS [Avg Hours], Format([Date Entered],"mmm yyyy") AS MonthName, Format([Date Entered],"mmm yyyy") AS DateT]

Based on the resuts of the "Setup Query" I will need to run the "Totals by Month" query and return its data to the Excel spreadsheet:

, Avg(qry_Totals_Split_Non_ECM.[Avg Hours]) AS [Avg Hours], Sum(IIf(IsNull([Hours]),0,1)) AS [# Cngs], qry_Dates.Dt AS [Month Name]]

Is ther anyway to do this from EXCEL VBA? Or can the two queries be combined into one in MS Access? You can see above I can run the "Setup Query" and return data to Excel...now I need to add the "Totals by Month" query. I will not need to return information from "Setup Query" to excel. I just used the code at the front of this message to see the communications worked. Ignore my typo in that code showing 2. I just need the final values produced by the "Totals by Month" query.

 

[SkipVought]

Dwane posted code that poses a method to accomplish this via Excel VBA. His parameter is supplied from D3 in the ActiveSheet.

My method probably does not require VBA. the SQL may need a little modifying, but the parameter(s) can be supplied directly from the sheet.

 

[dhookom]

I prefer Skip's method using MS Query but was attempting to show a modification of your existing code. I believe your code could change the SQL property of your saved query (faq701-7433) however if multiple users are running this at the same time it may lead to poor results.

There is no way we can determine if "can the two queries be combined into one in MS Access?" You would need to provide the SQL view of both queries. Then I expect someone like Tek-Tips query guru PHV could provide the SQL.

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

OK...we are getting closer I think. Duane, the code I provided was from the SQL view of my two queries. Can you be more specific as to what I am missing?

 

[dhookom]

I'm not sure I can be more specific than "You would need to provide the SQL view of both queries"

Duane
Hook'D on Access
MS Access MVP