vba Access ADO: 当对象关闭时不允许操作错误信息
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10838705/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Access ADO: operation is not allowed when the object is closed error message
提问by Rick
I am using Access 2003 with a local table which will later be transferred and linked to a SQL Server 2008 table. I am using the following code but get the error: "Run-time error 3704: Operation is not allowed when the object is closed" on the cnn.Execute line.
我将 Access 2003 与本地表一起使用，该表稍后将被传输并链接到 SQL Server 2008 表。我正在使用以下代码，但在 cnn.Execute 行上收到错误：“运行时错误 3704：对象关闭时不允许操作”。
sub test()
On Err GoTo Err_Sub
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim str As String
Dim strSQL As String
'Open a connection.
Set cnn = New ADODB.Connection
cnn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CurrentDb.Name & ";" & _
"Persist Security Info=False"
strSQL = Me.RecordSource
str = Mid(strSQL, InStr(strSQL, "Where "))
strSQL = "Update myTable SET Active = False " & str
Set rs = cnn.Execute(strSQL)
If Not rs Is Nothing Then rs.Close
Exit_Sub:
Set rs = Nothing
Exit Sub
Err_Sub:
MsgBox Err.Description
Resume Exit_Sub
End Sub
回答by DigCamara
Ran into this error as well (in my case I am using a Stored Procedure to retrieve some information). I had made some changes which caused the execution to malfunction.
也遇到了这个错误（在我的例子中，我使用存储过程来检索一些信息）。我进行了一些更改，导致执行出现故障。
The error disappeared when I put SET NOCOUNT ONas the first statement of the Stored Procedure.
当我把它SET NOCOUNT ON作为存储过程的第一条语句时，错误消失了。
回答by Tim Lentine
Try CurrentProject.Connection.Execute strSQLinstead of declaring your cnnobject.
尝试CurrentProject.Connection.Execute strSQL而不是声明您的cnn对象。
回答by Steve
Well that's simple. You need to add
嗯，这很简单。你需要添加
cnn.Open
somewhere before cnn.Execute also, don't forget to call
在 cnn.Execute 之前的某个地方也不要忘记调用
cnn.Close
Set cnn = nothing
before exiting from the sub
在退出 sub 之前
