Selecting Excel drop down to cause Access query using drop downs value

[Nade85]

I currently have an Access DB and Excel workbook that are linked and updating tables and graphs properly when I refresh from Excel. What I would like to happen is user selection from a drop down on the Excel worksheet launches the update of the Excel document via linked Access queries. The Access queries are currently run manually prior to the refresh, and they require a date entry to be completed. I have a drop down now built into my Excel worksheet with a list of years to be able to choose. What needs to happen after selection is something along the lines of the year value is taken from the drop down to be interpreted in Access as a criteria for the queries. Then, once the queries are finished executing in Access, Excel refreshes the tables and graphs as it does currently. I'm still pretty new to Access so I feel this is possible but I'm not 100% on it. And how to exactly go about doing it is obscure to me. I'm fairly certain it will require a macro in Access and Excel that work off each other, but the setup of any additional queries and the code in VBA and SQL are a mystery. Happy to provide existing layouts and coding to assist if anybody has a plan of attack on this.

Thanks.

 

[ranman256]

It would be easier to do it all in access:
pick the year, run the queries,
then it opens excel with the new data & graph.
(in acces you don't need to know VB, it can be done via macros)

Is this not possible?

 

[Nade85]

It's not impossible. The issue revolve more around who is using the data. I and one other person run basically copies of the same database and run our independent queries and modifications. Creates a back-up kind of scenario. When a new report/query is developed and flushed out properly we will consolidate for a period to have the same setups. The other side is due to the sensitivity of the information housed in the databases, our supervisory elements who require the access to the aggregate data and graphs are not able to have to access to the full Access databases. Nothing stopping them from looking but the shared aspect is the complication here. Since they often get requests for information from outside entities at random times, having the document be updated for them