Current VBA method to query table in Access and input data back in Excel is inefficient

Hello everyone!

Currently, i have working VBA that queries an Access table based on criteria in Column A of an Excel worksheet. The results of the query are then input into Column B. This process simulates a VLOOKUP from Excel to Access. The way the current VBA works is by looping through each cell in Column A and running the query for however many rows of data are in Column A and bringing the results back one-by-one.

Is there a more efficient alternative to looping? Access seems to query all records at once and wondered if i could simulate it instead of looping?

Below is the code. Please ignore the static loop range (2 to 100) and please ignore the on error resume next. The range will change every time. The range will typically be around 1,000 (but anywhere between 10 and 5,000). I have code to make the loop range dynamic and code to properly error trap, but wanted to get this working 1st.

Please Login or Register  to view this content.

maybe this will help...

I was thinking i should find a way to "link" the range in Excel to the field in the Access table. If i link them together, maybe i could somehow update all of column B in Excel with the query results from Access? Like a JOIN in SQL.

Does anyone know how to do that?

Many thanks!

Anyone have any tips on how to get a response?