Hi, I am trying to access some AS400 tables dynamically via MS Query. I have recorded a macro with one set of criteria, however I want the user to be able to amend this criteria without going directly into the query and just by entering the data in a cell in the spreadsheet. I cannot seem to be able to do it...Below is an extract from my code...any help greatly appreciated. I set up a named range for each of the bold criteria below and added them as a Dim String...but do not really know where to place in the code below as each time I try I get error messages in the code.
Many thanks
Range("Table_Query_2_from_xxxxx[AMAUCD]").Select
With Selection.ListObject.QueryTable
.Connection = Array(Array( _
"ODBC;DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=xx.xx.x.x;DBQ=xxxxx;DFTPKGLIB=QGPL;LANGUAGEID=ENU;PKG=QGPL/DEFAULT(IBM)," _
), Array("2,0,1,0,512;QRYSTGLMT=-1;"))
.CommandText = Array( _
"SELECT FTAMCPL3.AMAUCD, FTAMCPL3.AMCNCF, FTAMCPL3.AMBZNR, FTAMCPL3.AMB0NR, FTAMCPL3.AMBQNR, FTAMCPL3.AMAHST, FTAMCPL3.AMARCD, FTAMCPL3.AMD0CE, FTAMCPL3.AMAVCD, FTAMCPL3.AMAWCD, FTAMCPL3.AMABNB, FTAMCP" _
, _
"L3.AMA9CE, FTAMCPL3.AMI1NB, FTAMCPL3.AMAKCD, FTAMCPL3.AMAOCD, FTAMCPL3.AMG0DT, FTAMCPL3.AMBFTM, FTAMCPL3.AMG2CE, FTAMCPL3.AMUSR" & Chr(13) & "" & Chr(10) & "FROM SS1RESAV.xxxx.xxx FTAMCPL3" & Chr(13) & "" & Chr(10) & "WHERE (FTAMCPL3.AMD0CE='1268" _
, "635') AND (FTAMCPL3.AMG2CE='059110')" & Chr(13) & "" & Chr(10) & "ORDER BY FTAMCPL3.AMBZNR")
.Refresh BackgroundQuery:=False
End With
End Sub
Many thanks
Range("Table_Query_2_from_xxxxx[AMAUCD]").Select
With Selection.ListObject.QueryTable
.Connection = Array(Array( _
"ODBC;DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=xx.xx.x.x;DBQ=xxxxx;DFTPKGLIB=QGPL;LANGUAGEID=ENU;PKG=QGPL/DEFAULT(IBM)," _
), Array("2,0,1,0,512;QRYSTGLMT=-1;"))
.CommandText = Array( _
"SELECT FTAMCPL3.AMAUCD, FTAMCPL3.AMCNCF, FTAMCPL3.AMBZNR, FTAMCPL3.AMB0NR, FTAMCPL3.AMBQNR, FTAMCPL3.AMAHST, FTAMCPL3.AMARCD, FTAMCPL3.AMD0CE, FTAMCPL3.AMAVCD, FTAMCPL3.AMAWCD, FTAMCPL3.AMABNB, FTAMCP" _
, _
"L3.AMA9CE, FTAMCPL3.AMI1NB, FTAMCPL3.AMAKCD, FTAMCPL3.AMAOCD, FTAMCPL3.AMG0DT, FTAMCPL3.AMBFTM, FTAMCPL3.AMG2CE, FTAMCPL3.AMUSR" & Chr(13) & "" & Chr(10) & "FROM SS1RESAV.xxxx.xxx FTAMCPL3" & Chr(13) & "" & Chr(10) & "WHERE (FTAMCPL3.AMD0CE='1268" _
, "635') AND (FTAMCPL3.AMG2CE='059110')" & Chr(13) & "" & Chr(10) & "ORDER BY FTAMCPL3.AMBZNR")
.Refresh BackgroundQuery:=False
End With
End Sub