Look here for example codes
http://www.erlandsendata.no/english/...php?t=envbadac

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Francine Kubaka" wrote in message news:cOidb.11868$o21.615@edtnps84...
Yes!
That's what I do after each retrieval:

rs.Close 'Closing Recordset
cn.Close 'Closing ADODB Connection
Set rs = Nothing 'Destroy Recordset Object
Set cn = Nothing 'Destroy Connection Object

Do you know which objects are re-useable in the ADODB collections?

For example, I create a connection to Workbook A.

strConn = "......"
SQL = "SELECT APPLES....."
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open strConn
rs.Open SQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

Now I need to run a different query on the same Workbook:

Right now, I just do this:

SQL = "SELECT ORANGES....." 'I am creating a new SQL statement
rs.Close 'Close the old Recordset
rs.Open SQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText 'Open a
new recordset

Then, I need to run a query on a different Workbook:

strConn = "......" 'Create New Connection String
cn.Close
rs.Close
Set rs= Nothing
Set cn = Nothing
SQL = "SELECT PEARS....."
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open strConn
rs.Open SQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

That's what I do. And this seems to create persistent connections......

Is there something wrong? Am I creating duplicates somewhere? Am I failing
to destroy some objects?

Thanks!

FK








"Ron de Bruin" wrote in message
...
Hi Francine

Set .........= Nothing

Do you use this at the end of your macro???


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Francine Kubaka" wrote in message
news:BW9db.6471$o21.4784@edtnps84...
I have a long Excel VBA sub which creates several ADO recordsets from
another open workbook in succession.
Upon completion, the sub closes the source workbook. Despite that, the
VBE
still shows the source workbook open!
If I run the subroutine several times (and then close the source
workbook
programmatically or manually), several instances of one and the same
workbook appear in the VBE. Eventually, the system runs out of resources
and
the computer hangs.

I realize that I am failing to destroy one of the ADO objects somewhere
but
I do kill the ADO connection, set the recordsets to Nothing etc.

What can I be possibly be doing wrong??

Thanks!

FK