通过ADODB.connection连接到Excel中的SQL Server 2008时出现错误3709

I'm trying to open a recordset on a SQL Server in Excel using the below code, but get a 3709 - The connection cannot be used to perform this operation. It is either closed or invalid in this context. error. Where am I going wrong?:

我正在尝试使用以下代码在Excel中的SQL Server上打开记录集，但得到3709 - 该连接不能用于执行此操作。在此上下文中它是关闭的或无效的。错误。我哪里错了？：

EDIT:

编辑：

It is now working, i'll update the code below:

它现在正在工作，我将更新以下代码：

'Declare variables'
    Set objMyConn = New ADODB.Connection
    Set objMyRecordset = New ADODB.Recordset
    Dim strSQL As String

'Open Connection'
    objMyConn.ConnectionString = "Driver={SQL Server};Server=SERVER\SERVER; Database=we_ci_db; Trusted_Connection=Yes"
    objMyConn.Open

'Set and Excecute SQL Command'
    strSQL = "select * from tblUsers"

'Open Recordset'
    Set objMyRecordset.ActiveConnection = objMyConn
    objMyRecordset.Open strSQL

'Copy Data to Excel'
    ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)

2 个解决方案

#1

Remou pointed me in the direction of an alternative Connection String, but I still got the same results. I googled around for a bit and came up with another SO Question LINK with a different implementation, which resulted in:

Remou向我指出了另一个连接字符串的方向，但我仍然得到了相同的结果。我搜索了一下，然后提出了另一个SO问题链接，其中包含不同的实现，结果是：

'Declare variables'
    Set objMyConn = New ADODB.Connection
    Set objMyRecordset = New ADODB.Recordset
    Dim strSQL As String

'Open Connection'
    objMyConn.ConnectionString = "Driver={SQL Server};Server=SERVER\SERVER; Database=we_ci_db; Trusted_Connection=Yes"
    objMyConn.Open

'Set and Excecute SQL Command'
    strSQL = "select * from tblUsers"

'Open Recordset'
    Set objMyRecordset.ActiveConnection = objMyConn
    objMyRecordset.Open strSQL

'Copy Data to Excel'
    ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)

#2

That is not the connection string you need. it should read something like:

这不是您需要的连接字符串。它应该是这样的：

Provider=sqloledb;Data Source=SERVER\SERVER;Initial Catalog=cs_db;Integrated Security=SSPI;

See http://connectionstrings.com

见http://connectionstrings.com

#1