set nothing ado conn

[sal21]

to undersand...

is really required to use

conn.close
set conn=nothing

or simply

set conn=nothing

if yes i can reuse the access database to compact it via code(jro) withouth the conn.close?

[Rudi]

This was an interesting topic to read, and with the various articles I read this is a general consensus:
I hope it provides you with clarity.

The Close method tears down the memory structure.
Setting the variable to Nothing clears the pointer to that memory structure.

Theoretically, clearing the pointer should release the memory the pointer was referring to, because VBA uses reference counting for determining when it can release memory. Unfortunately, various things can go wrong and the reference count can end up out of whack, and memory won't be released even when it should be. Thus, to be sure you're not subject to memory leaks, or the weird kinds of bugs caused by implicit and unreleased references, you both Close and set to Nothing.

By using the "Close" method you are closing the connection to the database but is still in the memory where you can open again using the "Open" method.
Setting the recordset to "Nothing" on the other hand releases the object completely from the memory.

So, does doing one bypass the need for doing the other? Not really although you can bypass setting the recordset to nothing and will not encounter any error. Its just that it is the best practice to set the recordset to nothing after you closed it especially when you have no use to that recordset or you will not going to access the same recordset again.

My understanding was always that setting Conn to Nothing did not close the connection, it just removed it from that object, this is useful for things like Recordsets where you want a fixed read-only snapshot of the Recordset (combined with settings the correct cursor options) and so don't need to keep the connection live for that Recordset (but may need the connection still open for other operations).
Only actually calling objConn.Close closes the connection, and Set objConn = Nothing frees up the memory.

[HansV]

In short: you DO need to close the connection. Setting it to Nothing is nice but not required.