MS-Access linked table to AS/400 file – can’t retreive field descriptions/headings

120 pts.
Tags:
Access 2003
Microsoft Access
OS/400
I have created a linked table in MS-Access 2003 over an AS/400 file.  It works pretty well so far.  The problem I have is I only get field name and type.  I don't get any field description or heading information form the file.

Any suggestions on how I can retrieve the descriptive info as well?



Software/Hardware used:
OS/400, MS-Access 2003

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    Simple answer should be to query the database catalog, as with essentially every relational database. This is only a point of discussion, not an intended 'answer'. Tom
    125,585 pointsBadges:
    report
  • Craig Hatmaker
    Following up on Tom's answer - This SQL statement will retreive what you are asking for:
    Select * 
    From  QSYS/QADBIFLD
    Where DBILIB='<yourlibraryname>'
      And DBIFIL='<yourfilename>'
    
    NOTE: QSYS/QADBIFLD is using the DB2 seperator. If you are using SQL's convention it would be: QSYS.QADBIFLD. You can test this on your AS/400 using the STRSQL Command. You may also be interested in my blog that explains how to use Excel and VBA to query any database. Here is my blog: Beyond Excel If you use the code examples in my blog, you can modify them to run this SQL statement by changing the SQL statement in Macro1() to what is shown above, and changing the connection string to something like:
    "DRIVER={Client Access ODBC Driver (32-bit)}; SYSTEM=10.0.0.1; DBQ=*USRLIBL; NAM=1; CONNTYPE=0; TRANSLATE =1;"
    
    Where 10.0.0.1 is the IP address of your AS/400.
    1,705 pointsBadges:
    report
  • MBERIC
    Th is great info and I can say I didn't know about QADBIFLD. However, my problem is not having this field description when I create my linked table in Access. I only see the field name and the data type. Any thought on how I can get this to Access? Thanks
    120 pointsBadges:
    report
  • TomLiotta
    My personal preference would be to query SYSCOLUMNS rather than accessing QADBIFLD directly. I tend to be wary of direct access to system physical tables when IBM has provided indirect access that is a little more standard. Direct access means that you're getting into the physical objects used by DB2 itself. I'd hope that indirect access through IBM-supplied interfaces would give you a little more leverage with IBM Support if anything went wrong. Again, just personal preference. Tom
    125,585 pointsBadges:
    report
  • MBERIC
    Again, good information, but I'm having trouble seeing how this can help my situation. Here's what I have: - A file on the iSeries - An ODBC link to the iseries library - A MS Access 2003 databse - A linked table to this iSeries file. In design view on the file, all I see is the field name and its type. I do not get any description information. Therefore, I see cryptic field names and no online info of what this is. Okay for the IT guys who know the file, but not so good for the Accountants whom this is intended for. What I need to know is how to create a linked table in Access that shows the field descriptions from the iSeries file.
    120 pointsBadges:
    report
  • Craig Hatmaker
    I'm not sure what you want is possible. Hopefully, someone else will know a way. But on the off chance it is not possible here is my suggestion. 1) Manually get field definitions by querying the database catalog as Tom first suggested 2) Manually modify your MS Access form labels to use what your Accountants would understand most. NOTE: What the programmer put in the database definition may still be cryptic for Accountants - so being able to provide your accountants a custom description is probably what you want anyway. NOTE: Due to the legacy of the AS/400 it is likely you won't be able to find any 'relational' information between tables which is usually just as important, if not more important. So if you can get help from one of the IT guys who knows your tables will probably save you a lot of time and frustration. With that all said... As Tom suggested, query the database catalog for the field definitions. The easiest way I know of for a 'non IT guy' to query the database catalog is to use MS Query - which is included in MS Office. You have it. It's free. How to use MS Query is discussed in my blog. Again, using Tom's suggestion, the more standard way to access the database catalog is through the table QSYS2/SYSCOLUMNS. Add that table to MS Query, set the 'Filter' to 'TABLE_NAME' = <yourtablename>. You can now see the complete field descriptions for any table in your system. If you like, e-mail me at CHatmaker@CarlisleCarrier.com and I will send you an Excel Spreadsheet that does the same thing.
    1,705 pointsBadges:
    report
  • MBERIC
    Thanks for this info. I'm new at MS-Access, so I thought I was just missing something. The good news is this is exactly what I thought I'd have to do. The bad news is this is exactly what I thought I'd have to do. CHatmaker, I'll definitely take you up on your offer for the spreadsheet that makes this query simple. I'll be spending some time with my accountants to show them the relationships and help them build some base queries. At that time, we'll plug in the additional field level info. Tom, CHatmaker, thanks for the responses... Eric
    120 pointsBadges:
    report
  • TomLiotta
    Since we know nothing about the file (minimally described legacy DDS physical file? fully described SQL database TABLE? somewhere in between?), we don't know if such info is even available. That is, if the original developer didn't include useful text nor column headings, and no subsequent developer updated any descriptive element, then it simply doesn't exist. When a file is created as part of a fully contained application, there is far less attention paid to the poor guy who comes along later and wants to extract some decent meta-data. As the one coming later, you'd be stuck with the poor result. Tom
    125,585 pointsBadges:
    report
  • MBERIC
    Thanks, Tom.. The files are DDS descibed files and are documented with field descriptions and column headings. However creating a linked table to them only retrieves the field name and data type. It's not that I don't have the information, it's just that Access can't retrieve it. I can live with that and carry on. I just wanted to be sure it wasn't something I'd missed. Although I didn't get the anwer I'd hoped for, I did learn some good stuff. Unless someone finds a trick to retrieve this info into an Access linked table, we've done all we can here. Thanks, I appreciate everything you've passed along. Eric
    120 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following