Bind a form to access results from a stored procedure

Question

I am trying to return the results of a stored procedure to a form. I managed to iterate the results using the 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 

Answer 1

Ok, I checked this example. It includes changes in accordance with my setting that I left, instead of guessing your setting. 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 

Answer 2

Forget ADO. Create a passthru request in Access, with the property ReturnsRecords = True .
Link your form with this passthru request.
Using VBA, change the .SQL property of this QueryDef object, then open the form. All is ready.

 Set qry = CurrentDb.QueryDefs("myQryDef") qry.SQL = "exec spMyStoredProc " & "'argument1'" 

Answer 3

You need to use Set whenever you assign an object reference in VBA.

Change Me.Recordset = .Execute to Set Me.Recordset = .Execute .

In addition, you probably need to open it with a supported cursor type. I don’t think there is a way to change the type of cursor if you use the Execute method for the Command object. You will need to create a Recordset separately.

 Set rs = New ADODB.Recordset rs.Open cmd, , adOpenKeyset Set Me.Recordset = rs