vba 在 Excel 中通过 ADODB.connection 连接到 SQL Server 2008 时出现错误 3709
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11868824/
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
Error 3709 when connecting thru ADODB.connection to SQL Server 2008 in Excel
提问by aSystemOverload
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 - The connection cannot be used to perform this operation. It is either closed or invalid in this context.错误。我哪里出错了？：
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)
采纳答案by aSystemOverload
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 LINKwith a different implementation, which resulted in:
Remou 为我指明了替代连接字符串的方向，但我仍然得到了相同的结果。我搜索了一下，并提出了另一个具有不同实现的SO Question LINK，结果是：
'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)
回答by Fionnuala
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;
