Linked Table to AS/400 Member within a File

[Matthew Pfluger]

Has anyone been able to create a linked table to a Member within a File in an
AS/400 Library?

I have an Access 2007 database that uses an iSeries DSN to connect to our
AS/400, and I'm able to use the "External Data, Import, ODBC Table" command
and interface to browse a list of tables. However, the table that I'm really
looking for is a member within one of these files. I borrowed some VBA & SQL
code from one of the DBA's, and it reads like this:

' Make the connection to the AS/400
Set ISeriesDataCN = New ADODB.Connection
ISeriesDataCN.Open "Provider=IBMDA400;Data Source=OSK2;" & _
"Force Translate=0", "", ""
Dim cmdtext as String
cmdtext = "SELECT $BLEVL, $BLITM, $BQNTY " & _
"FROM CPJDDTA81.F553090(U60597)"
ISeriesDataCM.CommandText = cmdtext
Set objRecordSet = New ADODB.Recordset
Set objRecordSet = ISeriesDataCN.Execute(cmdtext, varParms, adCmdText)

Is there any way that I can create a linked table from this information?

Thanks,
Matthew Pfluger

 

[Rick Brandt]

Has anyone been able to create a linked table to a Member within a File
in an AS/400 Library?

An ODBC link to a multi-member table will always point at the first
member. To see any other member you have to create a view (or logical
file) on the AS400 that points to the member you want, then link to that.