SQL Server Connection problem

[Scotchpie]

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

[ranman256]

Is this a db password, or a workgroup password?

[Scotchpie]

Is this a db password, or a workgroup password?

Its a db password. When I log in to SQL Server I have no issues executing the stored procedure.

[ranman256]

you dont OPEN a recordset, you Set rst, and open connections: Set rs = con.Execute("qsMyQry")

[Scotchpie]

ranman256 you are a star. Thank's very much

[Aflatoon]

You can also open a recordset, but you need to specify the connection:

rs.Open "exec myStoredProcedure", con