Results 1 to 4 of 4

Pass Parameter from Excel through MS Query to MS Access Query

This is a discussion on Pass Parameter from Excel through MS Query to MS Access Query within the Excel Questions forums, part of the Question Forums category; I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured ...

  1. #1
    New Member
    Join Date
    Nov 2012
    Posts
    2

    Angry Pass Parameter from Excel through MS Query to MS Access Query

    I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.

    I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.

    Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.

    If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?

    Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.

    Thanks very much for your help!

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,142

    Default Re: Pass Parameter from Excel through MS Query to MS Access Query

    Hi,
    I believe the typical approach here would be to reproduce the query using MSQuery instead of Access. That is, if your query in Access is "SELECT X FROM Y WHERE Z" then you want to create a query with MSQuery that is also "SELECT X FROM Y WHERE Z". Then you can use a parameter cell or popup in Excel where the user puts in the date.

    It seems to (still) be explained well here:
    http://www.****s-clicks.com/excel/ExternalData6.htm


    Edit: hmm. Substitute d-i-c-k for the four asterisks in the URL above.
    See if that works for you - if not we can dig into some VBA to force it through one way or another.

    ξ


    Also covered here (perhaps better to start with):
    Daily Dose of Excel Blog Archive Parameters in Excel external data queries
    Last edited by xenou; Nov 26th, 2012 at 10:19 PM.

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    New Member
    Join Date
    Nov 2012
    Posts
    2

    Smile Re: Pass Parameter from Excel through MS Query to MS Access Query

    Thanks for the quick reply. I'm very familiar with using parameters with MS query, so I do understand the suggestion to do everything in MS Query instead of Access. However, the final query in Access is much more complicated that just one set of related tables and some fields/calculations. There are two summary record sets which are defined in separate Access queries, and these are related to three more tables in the final query I'm trying to call from Excel. Oh, and the parameter I need to pass to Access is used in both of the summary record sets.

    I suppose you could somehow copy all of the SQL code from the Access queries, nest it correctly together, and paste it directly into MS Query. That might be difficult...not sure.

    If you were going to do this with some VBA, what would you suggest?

    Quote Originally Posted by xenou View Post
    Hi,
    I believe the typical approach here would be to reproduce the query using MSQuery instead of Access. That is, if your query in Access is "SELECT X FROM Y WHERE Z" then you want to create a query with MSQuery that is also "SELECT X FROM Y WHERE Z". Then you can use a parameter cell or popup in Excel where the user puts in the date.

    It seems to (still) be explained well here:
    http://www.****s-clicks.com/excel/ExternalData6.htm


    Edit: hmm. Substitute d-i-c-k for the four asterisks in the URL above.
    See if that works for you - if not we can dig into some VBA to force it through one way or another.

    ξ


    Also covered here (perhaps better to start with):
    Daily Dose of Excel Blog Archive Parameters in Excel external data queries

  4. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,142

    Default Re: Pass Parameter from Excel through MS Query to MS Access Query

    I usually pull data from Access with VBA but this does have the downside that you don't have a "query table" - which means you don't get the automatic re-sizing, clearing out of old data, etc. etc. that comes with query tables. It's quite simple if you only need to pull data in. It can be a little more complicated if you need to "manage" the data coming in (i.e., you might need to clear the old data first, then pull in the new data).

    I'm a little confusing when you say the parameter is used in two summary recordsets - how do you get the parameter through to those queries? To speak generally, however, you can use DAO or ADO to get the data, which allows you to define a parameter that you pass to access when you make the call.

    Note, another hack would be this: instead of a parameter create a simple table that you update with the value. Then use it in your queries instead of an actual parameter (this may or may not be a good idea - I do this with report dates a lot since I have a lot of queries that all use the same parameter [Fiscal Period] and this way I update the default fiscal period in one table and all the reports are ready for the new month).

    Here's an example with DAO:
    Code:
    Sub DAOParamTest()
    Dim ws As DAO.Workspace
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim i As Long
    
    '---------------------------------------------
    'Note:
    'Set reference to Microsoft DAO Object Library
    '---------------------------------------------
    
    
        On Error GoTo ErrHandler:
        
        'DAO Workspace, Database, and Query Objects
        Set ws = DBEngine(0)
        Set db = ws.OpenDatabase("C:\myTemp\TestDB2.mdb")
        Set qdf = db.QueryDefs("Query1")
        
        'Set Parameter
        qdf.Parameters("[Enter ID]") = 2
        
        'Open Recordset
        Set rs = qdf.OpenRecordset()
        
        If Not rs.EOF Then
            
            'Clear old data (if rs always returns the same # of rows this isn't needed)
            Sheet1.Cells(1, 1).CurrentRegion.ClearContents
            
            'Write Headers
            For i = 0 To rs.Fields.Count - 1
                Sheet1.Cells(1, i + 1).Value = rs.Fields(i).Name
            Next i
            
            'Write Data
            Sheet1.Cells(2, 1).CopyFromRecordset rs
        End If
    
    
    'Close Workspace and clean up objects
    My_Exit:
    On Error Resume Next
    ws.Close
    Set rs = Nothing
    Set qdf = Nothing
    Set db = Nothing
    Set ws = Nothing
    Exit Sub
    
    ErrHandler:
    MsgBox Err.Description
    Resume My_Exit
    End Sub

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com