MS-Access-调用返回记录集的存储过程 [英] Ms-Access - invoke stored procedure that returns recordsset

问题描述

我正在为使用MS-Access 2007开发的大型旧系统而苦苦挣扎，我负责系统的维护(包括更改的实施).

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

系统与SQL Server(2012)进行交互.

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.

我尝试使用应用程序中已经存在的一些代码(使用ADO并将其复制到我正在使用的表单中)进行调用，但是我没有尝试过.

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.

这是我要使用的代码:

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

尽管我使用的字符串与整个应用程序中使用的字符串完全相同，但是执行以上语句会返回一条错误消息(类似于 ... not open ... ).

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

我开始怀疑我调用该函数的方式有问题(例如，未为其定义任何参数，并且期望与select语句相似的行为).

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

任何帮助将不胜感激.

编辑:

以下是描述我需要工作的代码段:

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

我得到的错误是:Error: Operation is not allowed when the object is closed.

推荐答案

您需要正确使用ADO和命令，而不仅仅是rs.Open存储过程.

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

使用以下Microsoft样板代码:

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()

其中Conn1是您的ADODB连接，并填写您的参数和存储过程名称.

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

这篇关于MS-Access-调用返回记录集的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！