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.

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.





"Edward" wrote:

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.