SQL Excel VBA 运行时错误 3709 无效连接

SQL Excel VBA Run-time Error 3709 Invalid Connection

提问by Comintern

This is my first question so constructive criticism is welcome! I am attempting to query an access database from excel vba and place the return information into an Excel range. I get this error:

这是我的第一个问题，欢迎有建设性的批评！我试图从 excel vba 查询访问数据库并将返回信息放入 Excel 范围。我收到此错误：

Error Message: "Run-time error '3709' The connection cannot be used to perform this operation. It is either closed or invalid in this context."

错误消息：“运行时错误 '3709' 连接不能用于执行此操作。它在此上下文中已关闭或无效。”

Code:

代码：

Sub Importfromaccess()
        Path = "C:\Users\myUser\Desktop\Database1.accdb"

        Set cn = CreateObject("ADODB.connection")

        cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Path & ";"

        Set rs1 = CreateObject("ADODB.recordset")

        rs1.activeconnection = cn

        Dim strSQL As New ADODB.Command

        strSQL.CommandText = "SELECT * FROM Tooling WHERE TID=BD0001"
        strSQL.CommandType = adCmdText

        Set rs1 = strSQL.Execute ' This is the line the error occurs on

        Sheets("Calc").Range("K1").CopyFromRecordset rs1
End Sub

I have enabled the following references:

我已启用以下参考：

1. Visual Basic For Applications,
2. Microsoft Excel 16.0 Object Library,
3. OLE Automation,
4. Microsoft Office 16.0 Object Library,
5. Microsoft Access 16.0 Object Library,
6. Microsoft ActiveX Data Objects 2.0 Library,

1. Visual Basic 应用程序，
2. Microsoft Excel 16.0 对象库，
3. OLE 自动化，
4. Microsoft Office 16.0 对象库，
5. Microsoft Access 16.0 对象库，
6. Microsoft ActiveX 数据对象 2.0 库，

I tried placing the line:

我尝试放置该行：

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Path & ";"

right before the error line and received this error:

就在错误行之前并收到此错误：

Run-time error '3705': Operation is not allowed when the object is open.

运行时错误“3705”：对象打开时不允许操作。

Anybody know what my problem might be?

有人知道我的问题可能是什么吗？

回答by Comintern

First (and unrelated to your error), unless you need to support clients using Windows 2000 or earlier, you should reference the highest Microsoft ActiveX Data Objects version instead of 2.0. If you're only using ADODB to interact with the database, you don't need the Microsoft Access 16.0 Object Library at all.

首先（与您的错误无关），除非您需要支持使用 Windows 2000 或更早版本的客户端，否则您应该引用最高的 Microsoft ActiveX 数据对象版本而不是 2.0。如果您仅使用 ADODB 与数据库交互，则根本不需要 Microsoft Access 16.0 对象库。

Second, if you already have a reference, don't create late bound objects like this:

其次，如果您已经有一个 reference，请不要像这样创建后期绑定对象：

Set cn = CreateObject("ADODB.connection")

Set cn = CreateObject("ADODB.connection")

Adding the reference early binds the type, so explicitly declare them and instantiate them using New:

提前添加引用会绑定类型，因此显式声明它们并使用New以下方法实例化它们：

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection

Your connection string should be fine - where you run into problems are these 2 lines:

您的连接字符串应该没问题 - 您遇到问题的地方是这两行：

    Set rs1 = CreateObject("ADODB.recordset")

    rs1.activeconnection = cn

    Set rs1 = CreateObject("ADODB.recordset")

    rs1.activeconnection = cn

Executing an ADODB.Commandwill return the Recordset, not the other way around. Remove those 2 lines entirely. Instead of attaching the connection to the Recordset, you need to use it when you're building your ADODB.Command:

执行 anADODB.Command将返回Recordset，而不是相反。完全删除这两行。Recordset您需要在构建您的 时使用它，而不是将连接附加到ADODB.Command：

    Dim strSQL As New ADODB.Command
    strSQL.ActiveConnection = cn      '<---Insert this.
    strSQL.CommandText = "SELECT * FROM Table1"
    strSQL.CommandType = adCmdText

Also, get rid of the Hungarian notation there - it's confusing as hell. An ADODB command isn't a String, so why should it be named strFoo?

此外，摆脱那里的匈牙利符号 - 它令人困惑。ADODB 命令不是String，那么为什么要命名为strFoo？

You also need to clean up after yourself - don't leave your recordset and connection just hanging open when you're done with them. Call .Closewhen you're finished.

您还需要自己清理 - 不要让您的记录集和连接在您处理完之后一直保持打开状态。.Close完成后打电话。

Finally, your SQL statement is most likely incorrect - you probably need to enclose your TIDin single quotes('):

最后，您的 SQL 语句很可能不正确 - 您可能需要将您的 SQL 语句括TID在单引号 ( ') 中：

"SELECT * FROM Tooling WHERE TID='BD0001'"

It should look closer to this:

它应该看起来更接近这个：

Sub Importfromaccess()
    Dim Path As String
    Path = "C:\Users\myUser\Desktop\Database1.accdb"

    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Path & ";"

    Dim query As New ADODB.Command
    query.ActiveConnection = cn
    query.CommandText = "SELECT * FROM Tooling WHERE TID='BD0001'"
    query.CommandType = adCmdText

    Dim rs1 As ADODB.Recordset
    Set rs1 = query.Execute ' This is the line the error occurs on

    Sheets("Calc").Range("K1").CopyFromRecordset rs1

    'CLEAN UP AFTER YOURSELF:
    rs1.Close
    cn.Close 
End Sub

回答by Rdster

You already Set rs1

你已经 Set rs1

How about trying something more like:

如何尝试更像：

Sub Importfromaccess()
  Dim strSQL As String, strPath as String
  Dim cn as Object, rs1 as Object

  strPath = "C:\Users\myUser\Desktop\Database1.accdb"
  Set cn = CreateObject("ADODB.connection")

  cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Path & ";"

  Set rs1 = CreateObject("ADODB.Recordset")

  strSQL = "SELECT * FROM Tooling WHERE TID='BD0001'"
  rs1.Open strSQL, cn

  Sheets("Calc").Range("K1").CopyFromRecordset rs1
End Sub

回答by Rdster

After some thorough rearranging I think I figured it out. I'm surprised at what changes fixed the problem but the following code works:

经过一些彻底的重新安排，我想我明白了。我对解决问题的更改感到惊讶，但以下代码有效：

Dim con As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As New ADODB.Command

cmd.CommandText = "SELECT * FROM Tooling WHERE TID='BD0001'"
con.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\myUser\Desktop\Database1.accdb;"

cmd.ActiveConnection = con

Set rs = cmd.Execute

Sheets("Calc").Range("K1").CopyFromRecordset rs

rs.Close
con.Close

The final error was fixed with:

最终错误已修复：

cmd.CommandText = "SELECT * FROM Tooling WHERE TID='BD0001'"

this line previously did not include single quotes around BD0001.

此行以前不包括 BD0001 周围的单引号。

I also added an ActiveConnection to the Command object.

我还向 Command 对象添加了一个 ActiveConnection。

Edit: This is the simplest working version of this I could manage courtesy of all you helpful people!

编辑：这是我可以管理的最简单的工作版本，感谢所有乐于助人的人！