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?