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.


Solution:

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.

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

    Err_MyProc:
        'Error handler here.
        Resume Exit_MyProc
    End Sub



No comments:

Post a Comment