Temptables in ADODB or DAO without spawning Access object (1 Viewer)

Hello,

I'm trying to figure out a consistent way to quickly execute a JOIN query between an ODBC data source and an excel range.

In this exemple, my ODBC source has a table with [material] and [EAN], I'm running and Excel macro that has about 5000 materials and I want to find their EAN codes. Doing it the easy way means running 5000 times the JOIN query in a loop. It's slow.

Through experience and experimentation I know that it's much faster to make an Access file, import the ODBC link, create a table and fill it with my 5000 materials and execute 1 JOIN query.

I want to have all of this happening in code, in the background, and leave nothing on the server.

My question is, is there a way to do this with only ADODB or DAO? Or will I need to create an Access application, new database, new tables, then kill the file on the server? I feel like I should be able to do something like this:

Obviously this is just a simplified view, but the idea is that. Can anyone advise me on this?
Actually creating a new Access project and building it in vba sounds clunky and unnecessary for a small query like such.

Thank you

What about an "in memory" recordset?

http://www.databasejournal.com/feat...p/3846361/Create-In-Memory-ADO-Recordsets.htm

You already know the answer, par. 3 of your post.

Uncle Gizmo said:

What about an "in memory" recordset?

http://www.databasejournal.com/feat...p/3846361/Create-In-Memory-ADO-Recordsets.htm

Click to expand...

That sounds interesting. I will research that possibility but at first glance it doesn't seem like you can run a joined query between a local recordset and a connection.

arnelgp said:

You already know the answer, par. 3 of your post.

Click to expand...

What do you mean?

it means you'll gonna code it anyway
why not in familiar MAccess.

"Through experience and experimentation I know
that it's much faster to make an Access file..."

I meant the execution time is much faster, when you put the data in a table and run 1 query instead of running 5000 independent queries. That's why I want to find a way to simulate that situation in Excel VBA.

are you trying to update an Excel file
with their EAN code from External DB?

if so you can just create another
excel file. make connection to the
External DB (DATA->New Query).

Use VLookup from your workbook without
EAN against the new workbook.

Save the new workbook and you'll just
need to press Refresh on the ribbon
the fetch the latest data.

much simplier.

Arnelgp,

I looked at this and it looks very promising. Thanks a lot for this idea, I had no idea you could do that in Excel.