How to COPY from one recordset to another?

Why copy the recordset when you can attach it?

By attaching the recordset, you get the benefit of all data changes as they happen without importing periodically and you get the structure automatically.

Lenny

Lenny, don't understand what you mean by attaching.
Need example code.
Thanks.

Open your databse container to TABLES

Then follow the menu item:
File->Get External Data->Link Tables

When the Link Dialog appears, on the bottom ("Files of Type") select ODBC Databases.

The remainder depends upon your environment. The Access help files will show you how to link an external table.

Once the table is linked (my terminology....attached), you inherit the structure and have a "live" link to the table on the AS400.

You only need to link once, so code is not necessary unless you will dynamically link various additional tables.

I can't link to the AS400 because all I need is a subset (extract) of the table.  Right now I'm doing the connection with an SQL statement for the criteria I need.
That result recordset is what I want to copy to a permanent Access table without having to READ/WRITE one record at a time.

FSOLL -

Use Jeff's solution for your immediate needs.

The reason that I suggested linking a table is that the nature of data is that when you perform an extract, the data is already obsolete. Data, by definition, is dynamic and ever-changing. So, every time that you want to examine your data, you would have to re-download an extract which has significant overhead, uses tremendous resources and takes a lot of time - especially if the table is huge.

If you linked a table, you could run your SQL statments or queries on the subset and you will be examining "fresh and current" data every time without invoking the extraction every time that you needed that data for your purposes. Remember, the size of a linked table has no impact on your Access database. You could even run yor SQL statements at the database server on the AS400 (by using stored procedures and triggers) and simply pass dynamically-extracted data to the Access application.

Just a thought.

Lenny

LennyGray,

Can you please stay on?
I sense that you have more experience with SQL.

My post assumes both tables are local.

I am not sure if you can point to a source from an AS400?

Jeff

FSOLL -

Has your question been clearly answered?


Lenny Gray