It's still a little vague: "how do I code the setup to start a VBA pass through query and how to get started with a recordset."
It sounds like you want to get data from the AS400, then process it locally and create reports from the local tables. Is that correct?
If I wanted to retrieve the Employees table from a SQL Northwind database, and populate a local Employee table in an Access Northwind database (a copy since there are PKs in the real table), and I was using a DSN called "SQL Northwind", but I only wanted employees from the UK based on a combo box on my form called cboCountry, that was not required, I would do this:
Code:
Dim sSQL As String
Dim sSQL_Local As String
Dim rs As ADODB.Recordset
Dim rs_Local As ADODB.Recordset
Dim cn As ADODB.Connection
Dim sCriteria As String
'Take parameter
If IsNull(Me.cboCountry) or Me.cboCountry = "" Then 'Country is text field
sCriteria = ""
Else
sCriteria = " WHERE [Country] = '" & Me.cboCountry & "'" 'assume UK was selected
End If
sSQL = "SELECT * FROM Employees" & sCriteria
'Clear out old data
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDELETE_Employees"
DoCmd.SetWarnings True
'Open connection to SQL Server Northwind
Set cn = New ADODB.Connection
cn.Open "DSN=SQL Northwind"
'Open recordset on Employees table in SQL Northwind
Set rs = New ADODB.Recordset
rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic
sSQL_Local = "SELECT * FROM Local_Employees"
'Open recordset on Access Northwind table (local connection)
Set rs_Local = New ADODB.Recordset
rs_Local.Open sSQL_Local, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
'Check for data and then transfer from SQL to Access Employees table
If rs.EOF = False Or rs.BOF = False Then
Do Until rs.EOF
rs_Local("LastName") = rs("LastName")
rs_Local("FirstName") = rs("FirstName")
rs_Local("Title") = rs("Title")
...
rs_Local.Update
rs.MoveNext
Loop
End If
cn.Close
DoCmd.OpenReport "rptMyCountryReport"...
This assumes that I don't need the EmployeeID.
Is that the sort of thing you want to do?