Parameter Query in Access from Excel

[rgreen511]

If possible, please help me with this somewhat unique problem.

I am trying to modify a report that is built in Excel. Currently, an Access DB is opened, and data is exported using several queries and macros to 4 Excel workbooks, then the Excel report is opened, and it pulls the data from the 4 workbooks, very clunky. I'm trying to pull the data directly from the database.

I can use Microsoft Query to try to query the existing Access queries, however, the queries are parameter queries requiring a date. Since the ODBC driver for Access queries does not pass parameters between the two programs, I am receiving a "Too Few Parameters. Expected 1" error. I looked in KB207143 and KB209203, which addressed the problem, but gave no workable solution.

An alternate solution would be to just duplicate the Access query in Microsoft Query, however there are several IIf statements in the Access Query, and these don't exist in Microsoft Query. Also, I can't find an easy way to change the dates for the query each time the report is run, it seems to lock the dates in. I tried to use a macro for the query, and use variables for the dates, however Office 2003 eliminated the xlquery.xla functionality, and now shows none of the dates or parameters in the VB query code, so there is no place to insert the variable dates.

Thanks, and sorry for the long post, but I've spent too long trying to figure this out already.

 

[rgreen511]

To clarify and expand my question:

This report is going to be used by several other people, so it must be semi-automatic (there's already a button to "run daily report").

I am trying to make the report such that Access does not need to be opened at all to update the data in the report. I would like the date to be chosen in Excel, and only the data for those dates input back into Excel.

I know a little VB and almost nothing about SQL, but I am willing to learn as much as I need if someone can provide a good resource, or some starting help.

I've read several threads that are very similar to this, but none that sounded like exactly my problem. If there is a thread that has already answered this, please direct me there.

Thanks again.