Does setting an ADO Command's ActiveConnection = Nothing close the underlying SQL Connection?
ado, adodb, asp-classic
Solution
My understanding was always that setting `ActiveConnection` 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)
AFAIK only actually calling `objConn.Close` closes the connection and `Set objConn = Nothing` frees up the memory
Problem
I have a customer who's Classic ASP application is generating ASP_0147 errors. The first thing I'm checking is that they're closing and releasing SQL/ADO resources timeously. Their code has the following pattern: ``` Function GetXXXXRecordSet() Set objConn = Server.CreateObject("ADODB.Connection") With objConn .CursorLocation = 3 ''adUseServer (default) .ConnectionString = strConnectionString .Open End With Set objCmd = Server.CreateObject("ADODB.Command") Set objCmd.ActiveConnection = objConn '' Build command object to call SQL stored proc, snipped for brevity Set objRs = Server.CreateObject("ADODB.RecordSet") objRs.Open objCmd, ,3,4 '' Cursor=adOpenStatic, Locktype=adLockBatchOptimistic '' Return Recordset Set GetXXXXRecordSet = objRs If Not objCmd Is Nothing Then objCmd.ActiveConnection = Nothing '' Should this use a Set statement? Set objCmd = Nothing End If If Not ObjRs Is Nothing The Set objRs = Nothing End Function ``` Does setting an ADO Command's ActiveConnection = Nothing close the underlying SQL Connection or does that have to be closed explicitly? Also should the line: ``` objCmd.ActiveConnection = Nothing ``` be: ``` Set objCmd.ActiveConnection = Nothing ``` Oddly the first version doesn't generate an error which is why I ask. It's been so long since I looked at ADO and my knowledge is somewhat rusty.