Bind Access Form To The Results From A Stored Procedure

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.. Her

Solution 1:

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 AsNew ADODB.Connection
Dim cmd AsNew ADODB.Command
Dim param1  AsNew 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
    EndWith

    txtSiteID_Search = 1If Nz(txtSiteID_Search, vbNullString) <> vbNullString ThenSet param1 = cmd.CreateParameter("@SiteID", adBigInt, adParamInput)
        param1.Value = txtSiteID_Search
        cmd.Parameters.Append param1
    EndIfWith cmd
        .ActiveConnection = cn
        .CommandText = "spSiteInformation_Retrieve"
        .CommandType = adCmdStoredProc
        SetMe.Recordset = .Execute
    EndWith

Solution 2:

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.

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

Solution 3:

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

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

Also, you probably need to open it with a supported cursor type. I don't think there's a way to change the cursor type if you use the Execute method on the Command object. You'll have to create the Recordset separately.

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