从Excel（VBA）运行存储过程的SQL Server 2005

I am trying to run a stored procedure on my SQL server. I think the connection type is 'ODBC', but I am unsure, I am trying the below:

我试图在我的SQL服务器上运行存储过程。我认为连接类型是'ODBC'，但我不确定，我正在尝试以下内容：

'Declare some variables
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim strSQL As String
Dim gway As String

'Create a new Connection object
Set cnn = New ADODB.Connection

'Set the connection string
cnn.ConnectionString = connectionString 'See http://connectionstrings.com if you need help on building this string for your database!

'Create a new Command object
Set cmd = New ADODB.Command
'Associate the command with the connection
'Open the Connection to the database
cnn.Open
cmd.ActiveConnection = cnn


VARIABLE= "param"
strSQL = "EXECUTE dbo.NAMEWASHERE '" & VARIABLE & "';"



cmd.Execute strSQL

'Close the connection again
cnn.Close

When I try to run this I recieve the error: Run-time error '3709';

当我尝试运行它时，我收到错误：运行时错误'3709';

Requested operation requires an OLE DB Session object, which is not supported by the current provider.

请求的操作需要OLE DB会话对象，当前提供程序不支持该对象。

I am sure I am just getting my connection type wrong somewhere, but could anybody help me out?

我确信我只是在某处弄错了我的连接类型，但有人可以帮助我吗？

To confirm, I'm trying to connect via ODBC to an SQLServer2005, via Excel VBA (2003)

为了确认，我试图通过ODBC连接到SQLServer2005，通过Excel VBA（2003）

Many thanks.

非常感谢。

UPDATE WITH FIX: You must open the connection before using ".ActiveConnection = """. I have editted the original code to show this change.

更新UPIX：您必须在使用“.ActiveConnection =”“”之前打开连接。我编辑了原始代码以显示此更改。

1 个解决方案

#1

UPDATE WITH FIX: You must open the connection before using ".ActiveConnection = """. I have editted the original code to show this change.

更新UPIX：您必须在使用“.ActiveConnection =”“”之前打开连接。我编辑了原始代码以显示此更改。