Searching through an Access database with Excel VBA

**Newbie_Nick** · 04-07-2010, 08:08 AM

I’ve written a macro to search through an Excel pricelist, compare currencies and prices and give me the best price and corresponding vendor for a list of products. Only trouble is that my data has grown exponentially in the last week and I currently have more than 65000 rows. I imported the data into Microsoft Access and would like to use my search VBA algorithm.

How do I do that? How do I open the Access Database from Excel and then apply my current code to it. I’m using ranges and rows – what I basically do is locate the first occurrence of the sought product, save its price, set a new search range to exclude the first occurrence, search for other rows containing the product and compare their prices to the first one and save the smallest of all values.

I really have no idea how to do that in Access. Any help will be appreciated!

Thanks
Nick

**romperstomper** · 04-07-2010, 08:43 AM

You'll basically have to start from scratch as the two apps are totally different. You would typically have an exchange rate table and then use a query to extract the lowest price from the product table (linking the two tables on currency code).