Operation is not Allowed when the object is closed

[okitamura]

I spent whole day trying to figure out this error. "Operation is not Allowed when the object is closed" and came with no answer. What I am simply trying to do is set a cursor for this particular record set. I know the code below works with simple SQL statement but as soon as I put in very long query in the strSQL variable. The below fails at the last line. I know that the "long query" is valid because it runs fine with Toad. If you someone could tell me what are the possible causes, it will help me greatly. I am so frustrated so any suggestion will be very much appreciated.

Thank you very much

Sam




Dim strConn, sSQL As String
Dim rs As ADODB.Recordset
Dim strDB, strUser, strPass As String

strDB = "XXX"
strUser = "YYY"
strPass = "ZZZ"

strConn = "Provider=MSDAORA;Data Source=" + strDB + " ;user ID=" + strUser + ";password=" + strPass

Set rs = New ADODB.Recordset
rs.Open strSQL, strConn, adOpenForwardOnly

 

[hmckillop]

How long is the "long query", is it longer than a string variable type - have you checked to see the string actually contains the whole query. - You also may need to check the datatypes for a DAO recordset string query to see what its max size is.

Secondly, why dont you create the "long query" as a stored procedure as you will be eating up network traffic passing large strings back and forward on a regular basis.

Apart from that, I dont see anything wrong with your code.

"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[okitamura]

Thank you very much for your response.

You mean: You also may need to check the datatypes for a ADO recordset string query to see what its max size is?

The resultant query is fine because I can run it in Toad.
For the statement "rs.Open strSQL, strConn, adOpenForwardOnly"
Is there a max size for strSQL value other thatn the max characters that a string variable can hold?

I guess this long-query is the cause but since I cannot touch the server, I cannot write procedure on the server side though it is a great idea.

My question is how do I know which object is closed and how can I open it. Or is there any way to open a record set other than the way I did using ADO and not DAO?

Thank you very much again for your help.

 

[vasudevansankar]

In your long query, at the top, try setting
Set NOCOUNT ON
This should solve the problem I believe.
Good luck