Search an Access database from Excel

[tonyfonda]

I have a very heavy MS Access database of 2 columns across 4m rows. Importing it to a workbook is not possible considering the number of rows.


I am looking to search for values in column 1 from an xls spreadsheet, and retrieve the corresponding value in column 2. A sort of VLOOKUP from excel to access.


Is this doable?

 

[Joe4]

Is it possible to do the search in Access instead, and then return the results to Excel?
All things being equal, that is probably how I would approach something like this.

Either that, or try to use VBA to create a Pass-Through Query to send to Access.
See: https://stackoverflow.com/questions...ry-from-excel-and-pass-paramerts-to-the-query

 

[tonyfonda]

Interesting, thank you.
Is there a possibility of linking the Access search value to an excel cell?
Say I want to retrieve 100 searches out of the 4m - it would be tedious to do it manually every day.

 

[Joe4]

You can link an Excel file to an Access table.
You can then use that table in a query to return what you want.
Typically, in Access, you would return these values simply by joining your two tables on the field you are trying to match.

 

[tonyfonda]

Thanks again - Joe.
This is somehow out of my depth considering that I am new here.
Do you have a template spreadsheet of some sort that I could start with?