Excel VBA. Changing a query critera via ADO

[PTP86]

Hi

The 2nd of today's questions.

I've got an Excel macro that runs lots of queries in Access and imports the results to the spreadsheet.
I've avoided writing complex SQL in VBA by building the queires in Access and then writing a simple select query in the VBA.

For 1 of the queries, I'd like the user to be able to specify part of the criteria in Excel and then the macro adjust the criteria accordingly (or another workaround)
There's 1 workaround I've thought of but it doesn't help if the user doesn't want that field in the criteria

e.g. Pretend there's an Access table that has the number of sales of each fruit on each day of the week.
The spreadsheet wants to give the user the option of choosing to import the sales for each fruit on just 1 day of the week (i.e. a criteria for [Day] would be set at say Monday), or import the sales for each fruit on each day (i.e. there would be no criteria for the [Day] field).

Is there a way of editing the query to remove the criteria from the [Day] field?


I know how to edit tables from within VBA so have thought about the workaround of having a Criteria table with a [Day] field and it is joined as part of the other query, and the VBA can edit the entry in the [Day] field. However, this doesn't solve the problem when the user is wanting there to be no criteria for the [Day] field.


Could anyone tell me how to edit a query from within Excel VBA or another work around?


Thanks

 

[PTP86]

Bumping this question back, plus condensing it a bit as no answer yet:

Using an ADO connection, is there a way of changing an Access query's criteria from within an Excel macro?


Thanks