Hope this helps..

Code:
Option Explicit
Public goConn As New ADODB.Connection

Private Sub Form_Load()
      Dim boolPerform As Boolean
      Dim rs As Recordset
      Dim rs2 As Recordset
      Dim strSQL As String

   ' Open the ADO Connection
      boolPerform = DataOpen(goConn)

   'Define the SQL String to Open Recordset with
   
     strSQL = "Select * from tblMyTable Order by CustomerID"
     
      'Define Recordset
      
      Set rs = New ADODB.Recordset
      
      'open Connection1
      rs.Open strSQL, goConn, adOpenKeyset, adLockOptimistic
      
      'Now, do the same for recordset 2.  It doesn't need another connection,
      'because the connection (goConn) to the table is already open.
      
      strSQL = "Select * from tblMySecondTable"
      
      Set rs2 = New ADODB.Recordset
      
      rs2.Open strSQL, goConn, adOpenKeyset, adLockOptimistic
     
      
End Sub

Public Function DataOpen(oConn As Connection) As Boolean
      On Error GoTo Open_EH
      
      oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                              "Data Source=" & "M:\Project Control\ProjectControl.mdb"
      oConn.Mode = adModeReadWrite
      oConn.Open
      
      DataOpen = True
      
      Exit Function
      
Open_EH:
      
      Call ErrorHandler(goConn)
      DataOpen = False
      Exit Function
      
End Function