Passing a variable from Excel to fire an Access Query (1 Viewer)

[daievans]

I'm running a VBA routine in Excel that loops through a lot of data. As part of the process, I'd like to pass a variable from Excel to an Access database that is open and have it run a query based on that value ... anyone ever done it? Is it feasible?

It's not how I would do this project if it were not for the fat that it's a legacy system whose owners have a certain pride of authorship

As usual, many thanks for your willingness to ponder my dilemma :banghead:

 

[Galaxiom]

What are you ding with the results of the query?

You can open a recordset directly from Excel without even having the database open.

 

[daievans]

What are you ding with the results of the query?

You can open a recordset directly from Excel without even having the database open.

Thanks for your response, G!

We're opening the Access database to verify (visually!) that the contents of a report are properly reflected in the Excel calculation. Ultimately, the goal will be to totally automate the process, but for now, they're wed to their status quo process.

So Essentially, I have excel on one screen and on the second screen I want to have access run a report that reflects the same client ID that we're processing in Excel.

Its very clunky, and alas nowhere near as sophisticated as what you were thinking! Hopefully, I can get the end-users to that point sooner rather than later

 

[daievans]

Here's another attempt at describing my challenge - I have an Excel Model that is running a looping calculation. As it loops through the dataset, I'd like to be able topass a value its currently processing into an Access query (The Database is loaded and open) as a criteria value and then execute that query ....

Does anyone have any ideas on the best course of action? Can I use DoCmd.OpenQuery?

 

[Galaxiom]

Build a datasheet form based on the query.
Use DoCmd.OpenForm and pass the criteria as the WhereClause argument.