Friday, 22 June 2012

9. Failing to close a recordset in VBA

It is poor programming to open anything without explicitly closing it. This problem is particularly acute in Access 97. Short of pressing Ctrl+Alt+Del, you may find that Access will not quit if recordsets or other objects are not closed and dereferenced.


Always close recordsets and set objects to Nothing in the error recovery of your procedure. Use this construct:
    Sub MyProc
    On Error Goto Err_MyProc
        Dim db as Database
        Dim rst As Recordset

        Set db = CurrentDb()
        Set rst = db.OpenRecordset("MyTable")
        'Useful code here.
        rst.Close            'Close what you opened.

        Set rst = Nothing    'Deassign all objects.
        Set db = Nothing
        Exit Sub

        'Error handler here.
        Resume Exit_MyProc
    End Sub

No comments:

Post a Comment