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