Receiving an error " The object you entered is not a valid record set property" Access 2013 assigning record set to a form

Question

I am using Access 2013 to upgrade an old application with SQL 2012 back end. I have several stored procedures with parameters which I need to call and assigned to Forms and Reports.

The issue I am having is that I get the error "7965" every time I try to assigned the returned record set to the Form. I am using the open event of the Form and the following code

Private Sub Form_Open(Cancel As Integer)


        Dim cmd1 As ADODB.Command
        Dim recs1 As New ADODB.Recordset
        Dim prm1 As ADODB.Parameter
        Dim prm2 As ADODB.Parameter
        Dim prm3 As ADODB.Parameter
        Set cnn = CreateObject("ADODB.Connection")
     
        cnn.ConnectionString = "DRIVER={SQL Server Native Client     11.0};SERVER=192.168.0.12;DATABASE=SavingsPlusCorp;Trusted_Connection=yes;"
     
    
        cnn.Open cnn.ConnectionString
        Set cmd1 = New ADODB.Command
     
    
        Set cmd1.ActiveConnection = cnn
    
     
    cmd1.CommandText = "dbo.iNVENSOLDSp"
    cmd1.CommandType = adCmdStoredProc
     
    Set prm1 = cmd1.CreateParameter("@branchid", adInteger, adParamInput, 2)
    cmd1.Parameters.Append prm1
     
    Set prm2 = cmd1.CreateParameter(" @Beginning_Date", adDate, adParamInput)
    cmd1.Parameters.Append prm2
     
    Set prm3 = cmd1.CreateParameter(" @Ending_Date", adDate, adParamInput)
    cmd1.Parameters.Append prm3
    
    Set prm4 = cmd1.CreateParameter("@vENDORID", adInteger, adParamInput, 2)
    cmd1.Parameters.Append prm4
    
    Set prm5 = cmd1.CreateParameter("@catID", adInteger, adParamInput, 2)
    cmd1.Parameters.Append prm5
    
     
     
    prm1.Value = Form_ReportGenerator.Branches
    prm2.Value = Form_ReportGenerator.Begin_Date
    prm3.Value = Form_ReportGenerator.Ending_Date
    prm4.Value = Form_ReportGenerator.Vendors
    prm5.Value = Form_ReportGenerator.Category
    
    
    Set recs1 = CreateObject("ADOdB.recordset")
     recs1.CursorType = adOpenKeyset
     recs1.CursorLocation = adUseClient
    'Set recs1 = cmd1.Execute
    'recs1.Open
    Set Me.Recordset = cmd1.Execute 

I have also tried

set me.Recordset= recs1

with the same results, please help.

The solution in the the thread identified as a possible is almost identical to my attempt. In fact I used it to build my attempt, I used the Execute command and assigned it to my form

Set me.RecordSet = cmd1.execute

It is this line that returns the error. Is their an issue with the provider I am using to connect? I don't understand why it is not working. Please help.

Answer 1

You need to call the Open method of an ADO.Recordset object.

To demonstrate this, first create a simple stored procedure in SQL Server.

USE AdventureWorks2016CTP3;
GO

CREATE PROCEDURE dbo.up_TestPerson
AS
    SELECT BusinessEntityID, FirstName, LastName
        FROM Person.Person;

Then create a Microsoft Access form with this code behind.

Private Sub Form_Open(Cancel As Integer)

    Dim cnn As New ADODB.Connection
cnn.ConnectionString = "DRIVER={SQL Server Native Client 11.0};SERVER=V-SQL16-R;DATABASE=AdventureWorks2016CTP3;Trusted_Connection=yes;"
    cnn.Open

    Dim cmd1 As New ADODB.Command
    Set cmd1.ActiveConnection = cnn
    cmd1.CommandText = "dbo.up_TestPerson"
    cmd1.CommandType = adCmdStoredProc

    Dim rst As New ADODB.Recordset
    rst.Open cmd1, , adOpenKeyset, adLockPessimistic

    Set Me.Recordset = rst

End Sub

Open the form, and it displays the data.

To demonstrate using a parameter, create a stored procedure like this. (Same as the first stored procedure, except it has a parameter and a WHERE clause.)

CREATE PROCEDURE dbo.up_TestPerson2
(
    @PersonType nchar(2)
)
AS
SELECT BusinessEntityID, FirstName, LastName
    FROM Person.Person
    WHERE PersonType = @PersonType;

Create another Access form with this code behind. (Same as the first form, except for the block where we create and configure the parameter.)

Private Sub Form_Open(Cancel As Integer)

    Dim cnn As New ADODB.Connection
    cnn.ConnectionString = "DRIVER={SQL Server Native Client 11.0};SERVER=V-SQL16-R;DATABASE=AdventureWorks2016CTP3;Trusted_Connection=yes;"
    cnn.Open

    Dim cmd1 As New ADODB.Command
    Set cmd1.ActiveConnection = cnn
    cmd1.CommandText = "dbo.up_TestPerson2"
    cmd1.CommandType = adCmdStoredProc

    ' This is new
    Dim prm1 As ADODB.Parameter
    Set prm1 = cmd1.CreateParameter("@PersonType", adWChar, adParamInput, 2)
    cmd1.Parameters.Append prm1
    prm1.Value = "EM"

    Dim rst As New ADODB.Recordset
    rst.Open cmd1, , adOpenKeyset, adLockPessimistic

    Set Me.Recordset = rst

End Sub

Open the form and it displays records matching the parameter.