ADO recordset error

[mkarja]

Hi,

I get the following error when trying to run a VBA code from excel.
----------- Error Start -----------
Run-time error '3704':
Operation is not allowed when the object is closed.
----------- Error End -----------


I'll put the code here and explain a bit more after that.
----------- Code Start -----------
Public Sub UpdateTable1()

Dim objConn As New ADODB.Connection
Dim objCmd As New ADODB.Command
Dim objRs As New ADODB.Recordset

With Worksheets("Compare")
objCmd.CommandText = "USE " & ComboBox1.Text & _
" select name from dbo.sysobjects " & _
"where OBJECTPROPERTY(id, N'IsUserTable') = 1 " & _
"and name <> 'dtproperties'"
objCmd.CommandType = adCmdText

Set objConn = GetNewConnection
objCmd.ActiveConnection = objConn

Set objRs = objCmd.Execute

Do While Not objRs.EOF
ComboBox3.AddItem (objRs(0))
objRs.MoveNext
Loop
End With

End Sub
----------- Code End -----------


That code works if I use some simpler query.
I've checked with SQL Profiler and the query comes to the
SQL Server as it should be. I've ran the query in Query Analyzer
and it works there.

I would really appreciate any help on this.

 

[mkarja]

That worked. Thank you very much.

I'm just curious why it didn't work with that query but worked with
a simple select query. Even when there was that USE <database> in the
beginning.