I am trying to return the results of a stored procedure to a form. I have managed to iterate thru the results using an ADO recordset, but cannot bind the results to the form..
Here is the VBA code:
Private Sub RetrieveSiteInformation()
Dim cmd As New ADODB.Command
Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset, f As ADODB.Field
With cnn
.Provider = "SQLOLEDB"
.ConnectionString =
"data source=UKFCSVR;initial catalog=ACACB;Trusted_Connection=Yes"
.Open
End With
Dim param1 As ADODB.Parameter
If Nz(txtSiteID_Search.Value, vbNullString) <> vbNullString Then
Set param1 = cmd.CreateParameter("@SiteID", adBigInt, adParamInput)
param1.Value = txtSiteID_Search.Value
cmd.Parameters.Append param1
End If
With cmd
.ActiveConnection = cnn
.CommandText = "spSiteInformation_Retrieve"
.CommandType = adCmdStoredProc
**' THIS FAILS**
Me.Recordset = .Execute
**' THIS LOOP WORKS FINE**
' Set rs = .Execute
' rs.MoveFirst
' For Each f In rs.Fields
' Debug.Print f.Name
' Next
' With rs
' Do While Not .EOF
' Debug.Print ![CompanyName] & " " & ![Postcode]
' .MoveNext
' Loop
' End With
End With
cnn.Close
End Sub
Okay, I have tested this example. It includes changes to suit my set-up which I have left in, rather than guessing at your set-up. Most of this is taken from http://support.microsoft.com/kb/281998/EN-US/
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim param1 As New ADODB.Parameter
With cn
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "SQLOLEDB"
.Properties("Data Source").Value = "Server"
.Properties("Integrated Security").Value = "SSPI"
.Properties("Initial Catalog").Value = "Test"
.Open
End With
txtSiteID_Search = 1
If Nz(txtSiteID_Search, vbNullString) <> vbNullString Then
Set param1 = cmd.CreateParameter("@SiteID", adBigInt, adParamInput)
param1.Value = txtSiteID_Search
cmd.Parameters.Append param1
End If
With cmd
.ActiveConnection = cn
.CommandText = "spSiteInformation_Retrieve"
.CommandType = adCmdStoredProc
Set Me.Recordset = .Execute
End With
Forget ADO. Create a passthru query in Access, with property ReturnsRecords = True
.
Bind your form to that passthru query.
Using VBA, change the .SQL property of that QueryDef object, then open the form. You're done.
Set qry = CurrentDb.QueryDefs("myQryDef")
qry.SQL = "exec spMyStoredProc " & "'argument1'"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With