logic to copy ODBC table to Access database


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: logic to copy ODBC table to Access database

  1. #1
    Vince Guest

    logic to copy ODBC table to Access database


    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


  2. #2
    Ingrid Schulz von Borkowski Guest

    Re: logic to copy ODBC table to Access database

    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
    >




  3. #3
    ObiWan Guest

    Re: logic to copy ODBC table to Access database


    > 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/





Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center