AS/400 pass through query from Microsoft Access

lionbank
5 pts.
Tags:
AS/400
Microsoft Access
I am trying to build a pass through query in Microsoft Access. I am trying to retrieve data from a file that has multiple file formats. Here is the code I have so far in my pass through query: SELECT h.lhamt1 balance, h.lhnote account FROM bnkprd01.lnp007 h WHERE h.lhtc1 = 33 AND h.lhposj BETWEEN 2012336 and 2012366 When I run the query I get a message that file LNP007 in BNKPRD01 has more than one format. The file format that is used in the AS/400 query is LNP0071. How do I specify to pull from that file format on the AS/400 in my pass through query?

Software/Hardware used:
Microsoft Access / AS400
Asked: Last updated:
Related Questions

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hello: Alias is OK

Example of VBA Code used.

Sub Update_ConnectionName()
    Dim NomeArray As String
    Dim Shieldname As String
    ‘On Error Resume Next
    Activeshield = ActiveWindow.Caption
    Shieldname = ActiveCell.Worksheet.Name

‘ Create DDM file on AS400
    NomeArray = “CREATE ALIAS SQLEXCEL.Namefile FOR Libname.Namefile (MbrName)”
   
    With ActiveWorkbook.Connections(“ConnectionName”).ODBCConnection
        .BackgroundQuery = False
        .CommandText = NomeArray
        .CommandType = xlCmdSql
        .Connection = Array(Array( _
        “ODBC;DRIVER={iSeries Access ODBC Driver};ODBC;” & Chr(13) & “” & Chr(10) & “DSN=Dsnname.dsn;BLOCKSIZE=1024;QRYSTGLMT=-1” _
        ), Array( _
        “;PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;LANGUAGEID=ENU;DFTPKGLIB=QGPL;DBQ=Libname Libname;SYSTEM=192.168.123.14;” _
        ))
        .RefreshOnFileOpen = False
        .SavePassword = True
        .SourceConnectionFile = “”
        .SourceDataFile = “”
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    Application.DisplayAlerts = False
    ActiveWorkbook.Connections(“ConnectionName”).Refresh
    Application.DisplayAlerts = True
   
‘ Run Query on DDM file
    NomeArray = “SELECT *  FROM EXCELTEMP.namefile
    With ActiveWorkbook.Connections(“ConnectionName”).ODBCConnection
        .BackgroundQuery = False
        .CommandText = NomeArray
        .CommandType = xlCmdSql
        .Connection = Array(Array( _
        “ODBC;DRIVER={iSeries Access ODBC Driver};ODBC;” & Chr(13) & “” & Chr(10) & “DSN=Dsnname.dsn;BLOCKSIZE=1024;QRYSTGLMT=-1” _
        ), Array( _
        “;PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;LANGUAGEID=ENU;DFTPKGLIB=QGPL;DBQ=Libname Libname;SYSTEM=192.168.123.14;” _
        ))
        .RefreshOnFileOpen = False
        .SavePassword = True
        .SourceConnectionFile = “”
        .SourceDataFile = “”
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    ActiveWorkbook.Connections(“ConnectionName”).Refresh
   
‘ Delete DDM file on As400
    ‘NomeArray = “DROP ALIAS SQLEXCEL.Namefile”
    With ActiveWorkbook.Connections(“ConnectionName”).ODBCConnection
        .BackgroundQuery = False
        .CommandText = NomeArray
        .CommandType = xlCmdSql
        .Connection = Array(Array( _
        “ODBC;DRIVER={iSeries Access ODBC Driver};ODBC;” & Chr(13) & “” & Chr(10) & “DSN=Dsnname.dsn;BLOCKSIZE=1024;QRYSTGLMT=-1” _
        ), Array( _
        “;PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;LANGUAGEID=ENU;DFTPKGLIB=QGPL;DBQ=Libname Libname;SYSTEM=192.168.123.14;” _
        ))
        .RefreshOnFileOpen = False
        .SavePassword = True
        .SourceConnectionFile = “”
        .SourceDataFile = “”
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    Application.DisplayAlerts = False
    ActiveWorkbook.Connections(“ConnectionName”).Refresh
    Application.DisplayAlerts = True
End Sub

Discuss This Question: 12  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.
  • philpl1jb
    The AS/400 file you are describing is a multi-format logical file .. each format points at a different physical file.    You need to determine and use the physical file and selection rules in your SQL not the logical file.    You can get the info you need with the command DSPFD lnp007 or from the DDS used to create the logical. 
    54,090 pointsBadges:
    report
  • philpl1jb
    Looks like creating an SQL Alais will allow you to use the "format"   http://www.as400tutorials.com/as400-multi-member-file-sql-workaround/
    54,090 pointsBadges:
    report
  • TomLiotta
    I don't know that CREATE ALIAS can handle multi-format files. It's okay for multi-member. I think that Phil's first comment about going to the actual underlying file that the desired format comes from is appropriate. I don't know if ODBC can handle multi-format files in any reasonable way since SQL doesn't handle multi-format files well, if at all. However, OLEDB could be used instead of ODBC. It would likely require redesign of your process. -- Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    Thanks Tom.  I was surprised to find an SQL Alais - Multi-Format link and it seemed like it was a credible source.
    54,090 pointsBadges:
    report
  • TomLiotta
    @Phil, I don't know that it can't work. I only see multiple references that it doesn't and I've never found an example where it does work. The current proposed 'Answer' doesn't show that it works, so I'm not sure why it was posted. -- Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    An attempt to use remote SQL against a multi-format LF gives:
    SQL State: 42857
Vendor Code: -7003
Message: [SQL7003] File TESTLFMF in TOML has more than one format. Cause . . . . . :   SQL cannot process a file unless it has only one format. Recovery  . . . :   Make certain that the correct filename was specified. Try the request again.
    There might be a method, but I don't know what it would be.   Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    Please scratch my Jan 24, 2013 4:53 PM (GMT) response.  The reference that I located did not apply to multi-format files but rather to multi-member files.   nuccio, I think you've mistaken the terms multi-format and multi-member.  AS/400 Multi-format logicals are a rather mature technology (but then so is Phil). 
    54,090 pointsBadges:
    report
  • philpl1jb
    LionBank - we stand by the Jan 24, 2013  3:54 PM (GMT) comment.    You must determine the physical file that is the source of the format you want and use that file directly instead of through the multi-member logical. 
    54,090 pointsBadges:
    report
  • philpl1jb
    "A question that sometimes drives me hazy: am I or are the others crazy?"Albert Einstein
    54,090 pointsBadges:
    report
  • nuccio
    helloI have posted the example and it workit have 3 steps:‘ Create DDM file on AS400‘ Run Query on DDM file‘ Delete DDM file on As400I use this method to transfer file from As400 to Access, Excel var __chd__ = {'aid':11079,'chaid':'www_objectify_ca'};(function() { var c = document.createElement('script'); c.type = 'text/javascript'; c.async = true;c.src = ( 'https:' == document.location.protocol ? 'https://z': 'http://p') + '.chango.com/static/c.js'; var s = document.getElementsByTagName('script')[0];s.parentNode.insertBefore(c, s);})();
    305 pointsBadges:
    report
  • TomLiotta
    @nuccio:   Running a query over a DDMF defined on a multi-format logical fails with:
    Message ID . . . . . . :   QRY1609 
 
Message . . . . :   File DDMLFMF in TOML is not a data base file, cannot 
  query. 
Cause . . . . . :   Only data base files can be queried.  Device files, for 
  example, do not have data and cannot be queried. 
Recovery  . . . :   Select a data base file instead of this file.
      And attempting to run CREATE ALIAS over a multi-format logical fails with:
    Message ID . . . . . . :   SQL7003 
 
Message . . . . :   File TESTLFMF in TOML has more than one format. 
Cause . . . . . :   SQL cannot process a file unless it has only one format. 
Recovery  . . . :   Make certain that the correct filename was specified. Try 
  the request again.
      If you have an example that works, please show the LF definition that includes two or more record formats. We'd like to see how it works.   Tom
    125,585 pointsBadges:
    report
  • nuccio
    From Nuccio:hi I had not understood well the question, my example refers to a multimember file, unfortunately it is not usable for multiple formats. Sorry
    305 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.

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

Following

Share this item with your network: