View Single Post
  #6 (permalink)  
Old July 15th, 2009, 08:28 AM
mmcdonal mmcdonal is offline
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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?
__________________
mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote