Import Access table rows based on Excel range

[Ppsych88]

I have an Excel dashboard into which I need to import data from an Access database. I have a range on Sheet 1 (“E116:E215”) which can contain the names of up to 100 individuals. The Access database contains information for over 20k individuals. I would like to query the Access database based on the content of the range in Excel (e.g., name) and return the entire Access table row for which there is an exact match in the Excel range. I’d like to put these Access rows on a separate Sheet.

Is anyone familiar with a way to do this? I am using Excel 2010 and Access 2010. I am a beginner with VBA, but am reasonably facile at modifying existing code to suit my purposes. I’ve done quite a bit of searching on this topic, but I must not be using the right words. Everything I’ve found so far is directed at either importing the entire table or using Excel to run an existing Access query. I have tried playing with the macro recorder and Microsoft Query, but I can't figure out how to make the query return information based on criteria in Excel, not Access.

Unfortunately, I cannot upload files due to my employer’s security policies.
Any help would be greatly appreciated.

[Kenneth Hobson]

Welcome to the forum!

Look for ADO methods. http://www.erlandsendata.no/english/...php?t=envbadac

[AlKey]

It would be easier if you create a query in Access and then use Excel (Data, From Access) to bring it into Excel. Or you can install Power Pivot, a free add-in from Microsoft and query Access table in Excel.

[Dimitrov]

I agree with AlKey. If you are new to VBA, then for the time being, you can run a query in Access and export the results of your query to Excel. In time, as you become more advanced, you can write VBA code to automatically bring the information from Access into Excel (without opening up Access). This involves some more concepts (e.g., ADO, recordset, connection, connection string, provider, and SQL). There are many resources (e.g., online sites and books) that you can use to acquire this knowledge. Some of the material is very technical but very fun to learn.

[alansidman]

One more option is to create the query in Access. In Excel, Click on Data, External connections and create a link between the query and Excel. See the query results in Excel.

Alan

[Ppsych88]

AlKey- I like the idea of using Power Pivot, but the Excel dashboard will be distributed to multiple users, none of whom have Power Pivot installed. The IT policy at my organization prohibits installing it.
Dmitrov and Alan- I thought about trying to handle the query from the Access end, but I wasn't sure it would work, since the names live in Excel. Are you saying I should make a query in Access that calls the names from Excel, then use Excel to run the query?

I appreciate the feedback so far, thanks!

[alansidman]

If you link the query to Access in Excel, you could then run a Filter (in Excel) on the information and show only the data for the individuals you want.

Are you looking to filter the data individually or on a group basis? ie. one at a time or multiple parameters?

If you are looking to do it on a single person at a time, then here is a link to some code that will allow a parameter query in Access from Excel.

http://datapigtechnologies.com/blog/...ry-from-excel/

One more possible means would be to use MSQuery to extract data from an Access Table/query.

[Ppsych88]

Thanks, Alan. I think I need to rethink my approach- what I am doing now is working, but I'm having to go about things in a overly complicated way since the end users for the spreadsheet aren't familiar with Excel. I was hoping to speed up a fuzzyVlookup function by limiting the number of rows it had to scan (by importing only selected Access table rows instead of the entire 20k rows in the table). I'll look into the filtering approach you suggested.

Thanks all for the suggestions - much appreciated.