Results 1 to 5 of 5
  1. #1
    century
    Guest

    AS/400 to Access (97)

    I'm managing to link or import tables from an AS400 to Access 97 but I would like to automate it such that I get a list of tables on the AS400 for a user to choose from to either link or import. Doea anybody have any ideas to this very frustrating problem?

    Andy

  2. #2
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AS/400 to Access (97)

    Andy:

    My first thought is that having links to all of the possible files your user might need to use would only increase your Access db's size nominally, so you might consider doing that. Shortest distance between two points, eh? But, if you're bent on getting a listing of all possible AS/400 files to link to, there are a couple of ways you could do it.

    (1)Create DSN's to all of the libraries you might need to reference, and teach your users not only how to link a table, but also teach them what libraries to find their files in. Oye! OK, so not such a great choice. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    (2)Get an AS/400 programmer to write a query that will walk the library you're interested in and get all of the file names (physical files are the only types you can link to, so no need to list logicals), then write them all to a table you can link to. You can show the user the list of files in that table, say in a list box on a form, and when they choose the file(s) they're interested in, create a link to it via code.

    There are an assortment of problems with all of these possibilites, though: 400 profiles and their permissions will dictate what a user can actually link, you'll need a DSN on each user's PC for each library they might need, and so forth. I hope that I helped clarify things a bit rather than muddy the situation...good luck!
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  3. #3
    century
    Guest

    Re: AS/400 to Access (97)

    Shane

    Many thanks for the solutions. I like the latter one as it has nore control and it is pretty much what I was looking for.

    Once again many thanks.

    Andy

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: AS/400 to Access (97)

    Isn't there an OLE DB provider for the AS400 for Access 2000? I can't remember, but it seems to me there is.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AS/400 to Access (97)

    Yup. It's an option in the Custom branch of the install process for Client Access Express; look for the Toolkit. I heartily recommend visiting the <A target="_blank" HREF=http://www-1.ibm.com/servers/eserver/iseries/clientaccess/toolkit/>Toolkit</A> portion of IBM's site and making your way the documentation.

    I've been able to pull data into Access and paint data into an ASP page using the OLE DB provider from Big Blue. Word to the wise, though, pass through queries or running the SQL natively on the AS/400 are your best options for complex queries or recordsets with, say, >25,000 records.
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •