从Excel VBA访问SQL Server存储过程（不包含SELECT语句），理想情况下不使用Excel数据连接？

I'm trying to build a VBA form that runs a SQL Server stored procedure to cache data before it does anything else. Ideally I'd prefer not to use an Excel data connection because then I can't export it to a new workbook with a minimum of fuss - it'd be nice to contain all of the information in the form's file.

我正在尝试构建一个运行SQL Server存储过程的VBA表单,以便在执行任何其他操作之前缓存数据。理想情况下,我不希望使用Excel数据连接,因为我无法将其导出到新工作簿中,而且最好不要大惊小怪 - 包含表单文件中的所有信息会很好。

The main way I know to connect VBA to a database is using code along these lines:

我知道将VBA连接到数据库的主要方法是使用以下代码:

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
cnn.Open ConnectionString
rst.Open QueryText, cnn, adOpenDynamic

/*Whatever you're doing to the data goes here*/

rst.Close
cnn.Close

The problem I'm running into is that the caching proc doesn't return a dataset (not even a blank one), and this type of connection seems to throw a tizzy if there is no data returned. And I'd prefer not to modify the proc if I don't have to.

我遇到的问题是缓存过程不返回数据集(甚至不是空白数据集),如果没有返回数据,这种类型的连接似乎会引发一个tizzy。如果我不需要,我宁愿不修改过程。

Is there a feasible solution, within these constraints? Or am I just being too whiny and should suck it up and use an Excel data connection or something?

在这些限制范围内是否有可行的解决方案?或者我只是太吵了,应该吮吸它并使用Excel数据连接或什么?

1 个解决方案

#1

I think you are looking for an ADODB.Command. Try something like this:

我想你正在寻找一个ADODB.Command。尝试这样的事情:

Dim cnn As New ADODB.Connection
Dim cmd As ADODB.Command

cnn.Open ConnectionString
Set cmd = New ADODB.Command

With cmd
    .ActiveConnection = cnn
    .CommandText = "EXEC spNameHere param1"
    .CommandType = adCmdText
    .Execute
End With