Error 3709 when connecting thru ADODB.connection to SQL Server 2008 in Excel
26,836
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:
'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)
Author by
aSystemOverload
Updated on July 05, 2022Comments
-
aSystemOverload almost 2 years
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?: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)