ADO SQL "Object Closed" Error

[MJPPaba]

In case you have seen this elsewhere, I accidently posted it on the Access VBA section, doh!

I am receiving an error message that I am not quite sure why.

In a constants module I have my connection string to an ADO-SQL Server 7 table, I have changed some details to protect the innocent.. please help.

Public Const myCostsConn As String = "Provider=SQLOLEDB;Initial Catalog=dbase;Data Source=server;User ID=userid;Password=password;"

In the procedure being called I have...
Sub btn_ChangeCostsData_Click()
Dim myADOConn As ADODB.Connection
Dim myADORs As ADODB.Recordset
Dim mySQL As String

Set myADOConn = New ADODB.Connection
Set myADORs = New ADODB.Recordset

mySQL = "Exec mySProc"
myADOConn.Open myCostsConn
myADORs.Open Source:=mySQL, ActiveConnection:=myADOConn

If Not (myADORs.BOF Or myADORs.EOF) Then

on the last line I get the debug error message ...
"Operation is not allowed when the object is closed."

MSDN tells me to check the state of the connection, but it is showing as open (1)

I just dont know, because even more strangely the code worked last night?

Any help greatly appreciated.

cheers

Matt

crazy times call for crazy people


CrazyPabs

Is it lunchtime yet?

 

[boggg1]

Be aware that the connection can be open but the recordset closed. I would do some debugging to confirm the connection is open but the recordset is closed at the critical point for some reason. There might be nothing wrong with your VBA, it could be the database which is doing you wrong.

Boggg1

 

[MJPPaba]

Hi boggg1

Thanks for your advice, last night I discovered what the problem was and now the process works like a dream.

The SQL was the problem, but there was a 'print @mysql' line just before the final recordset was returned. I overlooked this once I thought I had finished my work. I rem'd this out and now it works like a dream, ill put this down to 'Pilot Error'.

Thanks for your help though.

Matt

crazy times call for crazy people


CrazyPabs

Is it lunchtime yet?