Scotchpie
06-17-2014, 07:25 AM
Hi
I've been trying for hours to get this to work. I'm using Excel 2010 and connecting to SQL Server 2008. I'm trying to run a query and paste the results into a spreadsheet Here's my code:
Public Sub sqlCon()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
uid = "username"
pwd = "password"
con.Open "Provider=SQLOLEDB; Server=db.someserver.uk; Database=myDB; User ID=" & uid & "; Password=" & pwd &";"
rs.Open "exec myStoredProcedure"
ActiveWorkbook.Worksheets("Sheet1").Range("A1").CopyFromRecordset rs
rs.Close
con.Close
End Sub
I keep getting the following error "Runtime Error 3709: The connection cannot be used to perform this operation. It is either closed or invalid in this context."
The debugger highlights the rs.Open line.
Any help much appreciated.
Andrew
I've been trying for hours to get this to work. I'm using Excel 2010 and connecting to SQL Server 2008. I'm trying to run a query and paste the results into a spreadsheet Here's my code:
Public Sub sqlCon()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
uid = "username"
pwd = "password"
con.Open "Provider=SQLOLEDB; Server=db.someserver.uk; Database=myDB; User ID=" & uid & "; Password=" & pwd &";"
rs.Open "exec myStoredProcedure"
ActiveWorkbook.Worksheets("Sheet1").Range("A1").CopyFromRecordset rs
rs.Close
con.Close
End Sub
I keep getting the following error "Runtime Error 3709: The connection cannot be used to perform this operation. It is either closed or invalid in this context."
The debugger highlights the rs.Open line.
Any help much appreciated.
Andrew