CODEWITHSUNDEEP
sql-serverms-access
Idan
Idan

Reputation: 21

Ms-Access - invoke stored procedure that returns recordsset

I'm struggling with an old and massive system developed on MS-Access 2007, which I'm in charge of its maintenance (including the implementation of changes).

The system interacts with a SQL-Server (2012).

Currently, I need to implement a change in which a new stored procedure needs to be invoked which returns a simple records set, each record being a string.

I tried to use for the invocation some code that already exists within the application (using ADO and copied into the form I'm working on) but nothing I tried works.

Here is the code I'm trying to use:

glblsqlstrToLabels = "EXEC p_Labels_Print 1 , 2878954 , 'OC9991' , '89029' , 4 , 1 , 'dummy'"

Though I'm using exactly the same connection string as it is being used all over the application, executing the above statement returns with an error message (something like ...not open...).

I'm starting to suspect that there is something wrong in the way I'm invoking the function (e.g. not defining any parameters for it and expecting a behavior similar to a select statement).

Any help will be highly appreciated.

EDIT:

Following are the pieces of code describing what I need to have working:

        Dim RS As Recordset 

        ' Connection string is: Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=True;Data Source=****;User ID=****;Password=****;Initial Catalog=***;Data Provider=SQLOLEDB.1

        MyCommand = "EXEC p_Labels_Print 1 , 2878954 , 'OC9991' , '89029' , 4 , 1 , 'asdasd'"

        RS.Open MyCommand, CurrentProject.Connection

        Do Until RS.EOF
            Print <record retrieved>
        Loop

        RS.Close
        Set RS = Nothing

The error I get is: Error: Operation is not allowed when the object is closed.

Upvotes: 2

Views: 68

Answers (1)

Erik A
Erik A

Reputation: 32632

You need to properly use ADO and commands, you can't just rs.Open a stored procedure.

Use the following Microsoft boilerplate code:

   Set Cmd1 = New ADODB.Command
   Cmd1.ActiveConnection = Conn1
   Cmd1.CommandText = "sp_AdoTest"
   Cmd1.CommandType = adCmdStoredProc
   Cmd1.Parameters.Refresh
   Cmd1.Parameters(1).Value = 10
   Set Rs1 = Cmd1.Execute()

Where Conn1 is your ADODB connection, and fill in your parameters and stored procedure name.

Upvotes: 1

Related Questions