Pass Parameter from Excel through MS Query to MS Access Query

[Yoam69]

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!

 

[xenou]

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

 

[Yoam69]

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?

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

 

[xenou]

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:

[COLOR="Navy"]Sub[/COLOR] DAOParamTest()
[COLOR="Navy"]Dim[/COLOR] ws [COLOR="Navy"]As[/COLOR] DAO.Workspace
[COLOR="Navy"]Dim[/COLOR] db [COLOR="Navy"]As[/COLOR] DAO.Database
[COLOR="Navy"]Dim[/COLOR] rs [COLOR="Navy"]As[/COLOR] DAO.Recordset
[COLOR="Navy"]Dim[/COLOR] qdf [COLOR="Navy"]As[/COLOR] DAO.QueryDef
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]

[COLOR="SeaGreen"]'---------------------------------------------[/COLOR]
[COLOR="SeaGreen"]'Note:[/COLOR]
[COLOR="SeaGreen"]'Set reference to Microsoft DAO Object Library[/COLOR]
[COLOR="SeaGreen"]'---------------------------------------------[/COLOR]


    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] ErrHandler:
    
    [COLOR="SeaGreen"]'DAO Workspace, Database, and Query Objects[/COLOR]
    [COLOR="Navy"]Set[/COLOR] ws = DBEngine(0)
    [COLOR="Navy"]Set[/COLOR] db = ws.OpenDatabase("C:\myTemp\TestDB2.mdb")
    [COLOR="Navy"]Set[/COLOR] qdf = db.QueryDefs("Query1")
    
    [COLOR="SeaGreen"]'Set Parameter[/COLOR]
    qdf.Parameters("[Enter ID]") = 2
    
    [COLOR="SeaGreen"]'Open Recordset[/COLOR]
    [COLOR="Navy"]Set[/COLOR] rs = qdf.OpenRecordset()
    
    [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] rs.EOF [COLOR="Navy"]Then[/COLOR]
        
        [COLOR="SeaGreen"]'Clear old data (if rs always returns the same # of rows this isn't needed)[/COLOR]
        Sheet1.Cells(1, 1).CurrentRegion.ClearContents
        
        [COLOR="SeaGreen"]'Write Headers[/COLOR]
        [COLOR="Navy"]For[/COLOR] i = 0 [COLOR="Navy"]To[/COLOR] rs.Fields.Count - 1
            Sheet1.Cells(1, i + 1).Value = rs.Fields(i).Name
        [COLOR="Navy"]Next[/COLOR] i
        
        [COLOR="SeaGreen"]'Write Data[/COLOR]
        Sheet1.Cells(2, 1).CopyFromRecordset rs
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]


[COLOR="SeaGreen"]'Close Workspace and clean up objects[/COLOR]
My_Exit:
[COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
ws.Close
[COLOR="Navy"]Set[/COLOR] rs = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]Set[/COLOR] qdf = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]Set[/COLOR] db = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]Set[/COLOR] ws = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]

ErrHandler:
MsgBox Err.Description
[COLOR="Navy"]Resume[/COLOR] My_Exit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]