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.

 

[Jamie Collins]

Operation is not allowed when the object is closed.

The query does not return a row set. Try connecting to the database,
rather than the server, by specifying the initial catalog in the
connection string, then omitting the USE <database> from the SQL text.

Jamie.

--

 

[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.