Bind Form to ADODB.Recordset

[geedeearr]

Hi All,
I've used ADO in classic ASP (and ADO.NET in ASP.NET with Powershell -- not that they are all that similar), but it has been far, far too long. I can get the following code to return the recordset I am after, but I am having a devil of a time using this, with a Stored Procedure as my source, and binding it to an Access 2007 form. I've been searching and have gotten myself confused. Would someone please point me in the proper direction?
Thank you.

Code: Select all

Private Sub Form_Open(Cancel As Integer)
On Error GoTo ErrHandler:

    Dim cnMain As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rstForm As ADODB.Recordset
    Dim strConn As String
    
    strConn = "Provider=SQL Server Native Client 10.0;" & _
              "Data Source=MyServer;" & _
              "Initial Catalog=MyDatabase;" & _
              "Trusted_Connection=yes;"
    Set cnMain = New ADODB.Connection
    cnMain.ConnectionString = strConn
    cnMain.Open strConn
    
    Set cmd = New ADODB.Command
    With cmd
       .ActiveConnection = cnMain
       .CommandText = "[MySchema].[usp_MyStoredProc_SEL]"
       .CommandType = adCmdStoredProc
    End With
    
    Set rstForm = New ADODB.Recordset
    Set rstForm = cmd.Execute
    'Set Me.Recordset = rstForm
    
    With rstForm
        Do While Not .EOF
            Debug.Print rstForm!DBServerID
            Debug.Print rstForm!DBServerName
            .MoveNext
        Loop
    End With
    
ExitHandler:
    On Error Resume Next
    'Close any open db or rst objects and set to nothing here
    Set rstForm = Nothing
    rstForm.Close
    Set cnMain = Nothing
    cnMain.Close
    Exit Sub
    
ErrHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description & _
           " in procedure Form_Open of VBA Document Form_sbfrmServerWatch", vbOKOnly, "Form_Open"
    Resume ExitHandler
    Resume
End Sub

[geedeearr]

Thanks All,
With enough trail and error.........
The following works.

Code: Select all

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
On Error GoTo ErrHandler:

    Dim cnMain As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rstForm As ADODB.Recordset
    Dim strConn As String
    
    strConn = "Provider=SQL Server Native Client 10.0;" & _
              "Data Source=MyServer;" & _
              "Initial Catalog=MyDatabase;" & _
              "Trusted_Connection=yes;"
    Set cnMain = New ADODB.Connection
    cnMain.Open strConn
    
    Set rstForm = New ADODB.Recordset
    With rstForm
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockBatchOptimistic
        .Open "[Schema].[usp_MyStoredProc_SEL]", cnMain, , , adCmdStoredProc
    End With

   'Bind the Recordset to the form.
    Set Me.Recordset = rstForm

ExitHandler:
    On Error Resume Next
    'Close any open db or rst objects and set to nothing here
    Set rstForm = Nothing
    rstForm.Close
    Set cnMain = Nothing
    cnMain.Close
    Exit Sub
    
ErrHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description & _
           " in procedure Form_Open of VBA Document Form_sbfrmServerWatch", vbOKOnly, "Form_Open"
    Resume ExitHandler
    Resume
End Sub

[HansV]

Thanks for posting the solution - it may well help somebody else in the future!