Error 3709 when connecting thru ADODB.connection to SQL Server 2008 in Excel

Question

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)

The string itself wasn't wrong, I've tried several combinations which are completely different, but all work. It was the `rst.open` line that I didn't have right. I needed to use the other attributes, cnn, adOpenStatic etc... — aSystemOverload, Aug 13 '12 at 14:41

score 3 · Accepted Answer · edited May 23 '17 at 12:02

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)

score 0 · Answer 2 · answered Aug 08 '12 at 16:23

0

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

answered Aug 08 '12 at 16:23

Fionnuala

90,370
7
114
152

I've updated to the fields/values you've specified and it still says [3709 - The connection cannot be used to perform this operation. It is either closed or invalid in this context.] – aSystemOverload Aug 13 '12 at 12:50
I see you have MS Access, can you link to a table? Is this http://support.microsoft.com/kb/839428 relevant? – Fionnuala Aug 13 '12 at 12:52
You don't believe you can link to a table? – Fionnuala Aug 13 '12 at 14:10
Sorry, didn't answer your comment properly. I can link to the table in Access, either via VBA or manually. I do not believe that support item has anything to do with it. – aSystemOverload Aug 13 '12 at 14:32
Are you saying that the connection string you are using works in MS Access but does not work in Excel? – Fionnuala Aug 13 '12 at 14:34
I've updated the question. It would appear it wasn't the connection string that had the problem, but the implementation of the recordset. Thanks for your time. – aSystemOverload Aug 13 '12 at 14:44

Error 3709 when connecting thru ADODB.connection to SQL Server 2008 in Excel

2 Answers2