Performing Access query with Parameters from inside Excel
Posted on 2009-04-01
I need some help performing an access query in excel.
The access data is all in one table (and its about 3 million records) and I have the query with the right parameters set up and it works fine but I want to be able to run the query and get the data from excel because I have to perform several calculations on the data (so I don't want to build a form). I need the query to be dynamic - i.e. it chooses parameters from user entries in the worksheet.
I have tried using the get data "from other sources" through MS query, and it sort of works including looking up the parameters from the cells i tell it to, but it is painfully, painfully slow and sometimes the connections break.
Is there any other way, perhaps with some vba code or is the MS Query the only way?