Save Memory By Closing Open Recordsets

If you use any data objects in your code (DAO, RDO, or ADO), you should be sure to explicitly close all open recordsets, databases, and workspaces before you exit. Even though the pointers to these objects are automatically destroyed when you exit the program, if you fail to explicitly close all open items, your database connections may not be immediately released and the memory used by these objects may never be re-allocated by the operating system. Here's a short routine you can add to your Form_Unload event (or some other terminating code module) that will close all open DAO workspaces, databases, and recordsets and release the memory reserved by these objects. This code will work whether you have 1, 100, or even no connections open when you attempt to exit the form.

'--------------------------------------------------
Public Function CloseAllRecordsets() As Integer
'--------------------------------------------------
on error resume next
'Be sure to Close all Data Objects upon Exit - and release memory


'Put it on close of the Main Menu - (make sure you have no bound forms open)

Dim wsCurr As Workspace
Dim dbCurr As Database
Dim Rs As Recordset
Dim Frm As Form

For Each wsCurr In Workspaces
   For Each dbCurr In wsCurr.Databases
      For Each Rs In dbCurr.Recordsets
         MsgBox "The recordset " & vbCrLf & Rs.Name & vbCrLf & _
Rs.RecordCount & " record(s) was left open - now closing it.", vbCritical, "Validation"
         Rs.Close
         Set Rs = Nothing
      Next

      dbCurr.Close
      Set dbCurr = Nothing
    Next

    wsCurr.Close
    Set wsCurr = Nothing
Next

End Function

Published January, 1999