Pass parameter from Excel to Access query

Question

I have a Access (2010 but I've also tried Office 2003) database with a lot of tables, forms and nested queries which rely on data entered in a form, e.g. WHERE query1.year=[Forms]![Form1]![Text0];. I now want to link such a query to Excel as external datasource but this makes problems:

If I use the standard approach "Data -> From Access", those queries which rely on forms don't even show up. If I create a link to another query and afterwards change the Command text in the connection properties window to point to the query I actually want, I get an error message ("The query did not run, or the database table could not be opened.").

If I use MS Query ("Data -> From Other Sources -> Microsoft Query"), I can select the query I want but get the error message "Too few parameters. Expected 1."

This, of course, makes perfect sense because the query relies on a form which isn't available in Excel. I tried to use "real" query parameters in those queries but it's the same thing. I really want Excel to take the value in a given cell and pass it to the Access query as parameter.

So the ideal world would look like this: In Access I have a bunch of queries which rely on parameters to do their work. Within Access I can use a form to let the user enter those values. From Excel those parameters should be taken from given cells. Is this possible at all?

Solution 1

Captain,

It's possible, I'm 90% sure, to do this using a DAO.querydef and it's "parameters" property. You could also rewrite your queries without parameters and then use the Query Manager in Excel. For help on both these ideas, you could start at http://www.dailydoseofexcel.com/archives/2004/12/13/parameters-in-excel-external-data-queries/.

Solution 2

I never reference a form control in a saved query unless it's absolutely necessary, for the very reason you've encountered -- it makes the queries less widely usable by virtue of the outside dependency on the form being open.

Access provides multiple ways of applying criteria to recordsets at runtime, so there's almost never any need to hardwire a dependency on a form control.

That said, if you can connect to your database with DDE, it should be able to do the job. DDE used to be the default for Word merge from Access databases (to my eternal annoyance). It launches a new instance of Access (which means that if you have user-level security in place, it will sit and wait with a username/password prompt and never open your database), and then communicates with it via old-style DDE commands. In your case, it ought to work if you open the needed forms in your database's startup utilities. However, if they need to have data filled out by the user at runtime, then that won't work, either.

The ultimate solution is to decouple your saved queries from the forms, or to use the raw SQL string within Excel to retrieve the data (absent the form reference).