From Excel, search Access database and return all matches to a variable value

Hello. This question relates to using Excel as a front end user interface to view data stored in an Access database. Let's say that I have a database in Access that lists different types of fruit (field 1) and their color (field 2). I would like the Excel user to be able to type a color in an Excel worksheet field A2, let's say "red". Excel would then list all the red fruits in Column C, either via formulas housed in Column C fields or via VBA. I know I could do this by creating a linked table in another Excel worksheet that would display all the Access data and then using a formula such as VLOOKUP to return all the matches, but I'm wondering if it is possible to do this more simply and straightforward without having to first import the entire linked data table into Excel. Whatever solution is suggested needs to work across different versions of Excel (2007-2016) and should be able to look through several thousand records very quickly to find matches. Thanks in advance for the help!

This is more a pointer than a solution.

Do you know how to write and run SQL queries? If you do, you can simply run a statement in excel as if you are working in Access. I do not know much about Access's Stored procedures, but I know this can be done in SQL Server. The Stored procedure is written and stored in database, but this function can be called from Excel. What is more, you can easily refresh and update excel whenever the database has been updated.

Something like this. Change sCnn to the filepath of you db. Also depending on what Access data base you are using change for your needs. .2003 Access Use Jet
2007 db or higher use ACE

Please Login or Register  to view this content.