Hi, I have been using the ADO data model in an ADP and have used two approaches (both are working) in terms of where I dim the connection and recordset object and where i actually create an instance of them. Here is an example of both approaches i have used:

Code:
Option Compare Database
Option Explicit
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim rs2 As New ADODB.Recordset

    Dim intUserid As Integer

Private Sub Exit_Click()
    Call CleanUp
    DoCmd.Quit
End Sub

Private Sub Form_Load()
    On Error GoTo Err_Form_Load
    Dim strSQL As String
    
    cn.ConnectionString = CurrentProject.Connection
    cn.Open
    
    strSQL = "select user_id, user_fname + ' ' + user_lname as user_name from tblusers where sign_on ='" & fOSUserName() & "';"
    rs.Open strSQL, cn

OR


Code:
Option Compare Database
Option Explicit
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim rs2 As ADODB.Recordset

    Dim intUserid As Integer

Private Sub Exit_Click()
    Call CleanUp
    DoCmd.Quit
End Sub

Private Sub Form_Load()
    On Error GoTo Err_Form_Load
    Dim strSQL As String
    
    Set cn = New ADODB.Connection
    cn.ConnectionString = CurrentProject.Connection
    cn.Open
    
    strSQL = "select user_id, user_fname + ' ' + user_lname as user_name from tblusers where sign_on ='" & fOSUserName() & "';"
    
    Set rs = New ADODB.Recordset
    rs.Open strSQL, cn

I was leaning towards the second example where i dim the object variables at the form level and actually wait to create the instances of the objects in the sub procedures as i need them. ALthough not shown, in every sub procedure i close and set to 'nothing' all my recordset and connection objects.