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?
Best Answer
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/.