Operation is not allowed when the object is closed

[Edward]

I am getting data from DB2 and importing into excel. It seems that
when I pull a lot of data I get an error. In debug mode when I look
at the recordset items it says <Operation is not allowed when the
object is closed>.

My code looks like this:

Dim oConn As ADODB.Connection
Dim oRs As ADODB.Recordset

sConn = "Persist Security Info=False;Database=MYDB;" & _
"Driver={IBM DB2 ODBC DRIVER};" & _
"Protocol=TCPIP;Hostname=MYDB.XXX.COM;Port=446;" & _
"USER ID=" & userid & "; PASSWORD=" & password & ";"

Set oConn = New ADODB.Connection
oConn.Open sConn
Set oRs = New ADODB.Recordset
oRs.Open aSQLCmd, oConn, adOpenStatic, adLockReadOnly, adCmdText

Any idea what is wrong?

Thanks.

 

[Patrick Molloy]

this message is because of either (1) losing the database connection or (2)
trying to reopen an already opened recordset or (3) a 'bad' T-SQL staement
that the server rejects or (4) something else.
Looks like (4). So try to add a limit to the records returned say TOP 50
eg
"SELECT TOP 50 [ID] FROM MyTABLE"

at least then you#d prove the SQL was good.

then try
"SELECT COUNT(*) FROM MyTable"
to see how many records you'd get if it didn't break.