Load data from ADO recordset into local Access Table

[storm75m]

Hello masterminds...

This seems like it should be pretty simple. I need to take an ADO recordset object "rsAccts" (data from AS400) and load it into a local Access table "tblAcctCodes". I know this can be accomplished by looping through the recordset and running an INSERT statement for each record in the recordset, but that seems like it would be unnecessary. Or maybe that is the only way to do it. I have to run this several hundred times (within a loop), so the least amount of code would be the best. It would be nice if this can be done through a simple line of code like:

Set tblAcctCodes.recordsource = rsAccts

I'm I dreaming or what?

Any help would be much appreciated, code samples are always awesome! Thanks in advance!

 

[RobCPA]

Can you link to the AS400 table via ODBC? If so, you could simply write an append query to select data form the AS400 table into your Access table. That's what I do with tables that are in HP3000 (Amisys).

 

[storm75m]

Well I'm going to be connecting to hundreds of different tables, so I don't want to have hundreds of different linked tables or pass-through queries in my database.

There have also been other instances where I would like to just dump a recordset into a table so I could work with it later... but it's starting to look like this isn't possible.