Comparing Variant Array to ADODB.Recordset?

I'm hoping someone can help out a SQL rookie here.

I frequently have the following scenario: I have a variant array of values that I need to compare to a database. Currently I am passing the array to a VBA function, the function sets the ADODB connection and then loops through the array. On each loop, it alters the SQL query to the current value from the array and then retrieves a recordset. (If it helps visualise what I am doing, I have posted the code at the bottom of this post)

I feel that this is inefficient and it would be much quicker to match the arrays values in SQL instead.

Is it possible to somehow 'join' the array with the SQL query before retrieving the recordset?
Could the array be converted to a recordset and then somehow 'join' the recordset??
Any other method I am not aware of?

UPDATE: Joining the database to the arrays source is not an option.


APPENDIX:

Please Login or Register  to view this content.

Originally Posted by Kyle123

Without seeing the SQL it's impossible to say,

Well I have a number of different projects that make use of the function I am using for now and all of their SQL queries are different. The one thing in common is that they are all SELECT queries and they end with the WHERE criteria = ' (The VBA function repeatedly calls this query with each value in the array)

Originally Posted by Kyle123

but can't you just use an in clause with all your criteria?

You mean write the SQL so it's like '...WHERE field IN ( 1d array )' ? I hadn't thought of that! Hmmm I would need to convert the array to a string first I guess... I'll play around with this idea and see what happens.

UPDATE: Perfect. Thank you very much Kyle. The speed increase turned out a lot better than I hoped for. The macros using the new function are now up to 15x faster! If anyone needs this, see my current WIP below. If you found this useful, please rep Kyle in the previous post!

Please Login or Register  to view this content.