INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Run a parameter Access 2003 Query from Excel

Run a parameter Access 2003 Query from Excel

(OP)
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.

RE: Run a parameter Access 2003 Query from Excel

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

CODE --> vba

Sub Run_Access_Query_Test()
    Dim strSQL As String
    ' Your actual SQL goes in the next lines
    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") & "'"

    'Step 1: Declare your variables
     Dim MyDatabase As DAO.Database
     'Dim MyQueryDef As DAO.QueryDef
     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"
     
    
     'Step 2: Identify the database and query     
     Set MyDatabase = DBEngine.OpenDatabase(strMDBFile)
     '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 = MyDatabase.OpenRecordset(strSQL)
     
    'Step 5: Clear previous contents
     Sheets("Test").Select
     ActiveSheet.Range("A6:K10000").ClearContents
    
     'Step 6: Copy the recordset to Excel
     ActiveSheet.Range("A7").CopyFromRecordset MyRecordset
    
     'Step 7: Add column heading names to the spreadsheet
     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

RE: Run a parameter Access 2003 Query from Excel

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.

RE: Run a parameter Access 2003 Query from Excel

(OP)
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:

CODE --> Complete

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:

CODE --> Hours

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

RE: Run a parameter Access 2003 Query from Excel

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.

RE: Run a parameter Access 2003 Query from Excel

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: How to Change SQL property of saved query (DAO)) 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

RE: Run a parameter Access 2003 Query from Excel

(OP)
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?

RE: Run a parameter Access 2003 Query from Excel

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close