logic to copy ODBC table to Access database

[Vince]

I've got a couple years of VB experience and am always looking for the best
way to do things. What is the best way to copy the entire contents of a table
on our AS/400 (ODBC datasource) to an MS-Access database. I kow I can set
up SQL statements to loop through the source record set and use the Addnew
after building a long string but I wonder if there is a simpler way. both
tables and all fields are the same name and type, it's a straight clone task
of about 30 tables.
Using VB6

Thanks,

Vince

[Ingrid Schulz von Borkowski]

Hi Vince,

I have done this not with AS/400 to Access but with Oracle to Access
(prgramatically).

If you have to do this only once, then, in the access database create links
to all the tables in the AS/400 databse and then for each access table
create append queries (selecting from the linked Tables) and execute them.

When you have to do this more often you can do this programatically,
creating the links and run the queries.

Kind, regards
Ingrid

"Vince" <browerv@mohawkpaper.com> schrieb im Newsbeitrag
news:3cd946c0$1@10.1.10.29...
>
> I've got a couple years of VB experience and am always looking for the
best
> way to do things. What is the best way to copy the entire contents of a
table
> on our AS/400 (ODBC datasource) to an MS-Access database. I kow I can set
> up SQL statements to loop through the source record set and use the Addnew
> after building a long string but I wonder if there is a simpler way. both
> tables and all fields are the same name and type, it's a straight clone
task
> of about 30 tables.
> Using VB6
>
> Thanks,
>
> Vince
>

[ObiWan]

> I've got a couple years of VB experience and am always looking for the
best
> way to do things. What is the best way to copy the entire contents of a
table
> on our AS/400 (ODBC datasource) to an MS-Access database. I kow I can set
> up SQL statements to loop through the source record set and use the Addnew
> after building a long string but I wonder if there is a simpler way. both
> tables and all fields are the same name and type, it's a straight clone
task
> of about 30 tables.
> Using VB6

Well, you could link the tables from the AS/400 database and use some
queries to transfer the data from the linked tables to the local ones, but
in my experience I can say that (depending on number of records) this
won't be a "fast" operation; the fastest way I found to do this is the
following:

use FTP do download the files from the AS/400 onto a local folder; link
the (text) files to the access DB and -then- perform the data import;
this will be two to five times faster than using ODBC to perform the
transfer; keep in mind that the FTP transfer must be done using "ASCII"
mode so that the AS/400 will spit-out ascii data and not EBCDIC. The
naming convention to get the file will be /LIBRARY.LIB/TABLE.FILE, that
is supposing the library on AS/400 is named "TEST" and the table name
is TESTTBL, your file request should look like:

/QSYS.LIB/TEST.LIB/TESTTBL.FILE

as a last note, this method won't work with binary (packed) fields; this
means that you'll either need to create some intermediate tables on the
AS/400 containing only "zoned" columns or go back to the slower ODBC
solution.

A good (and free) ActiveX component to implement FTP transfers can
be found here (along with other interesting components):

http://www.eurosource.se/akhe/activex/