Top Contributors

Enclosing ressources in RAII capsules


One of the recurrent issue I'm facing at work, where I have to maintain a bunch of access databases (sadly, hugly written), is applications leaving lots of garbage behind (queries created on the fly not deleted, Office application not closed properly ect .....).

So I'm thinking about enclosing these "ressources" in RAII classes, this way ressources are automatically acquired and released.

Sample code with QueryDef:
Create a table named GUID, add an ID column, autonumber, primary Key.
(this table is used to generate unique names).
    '// Query class module
Option Compare Database
Option Explicit

Private mDb as DAO.Database
Private mQd As DAO.QueryDef

Public Class_Initialize()
    Set mDb = CurrentDb

    Dim rs As DAO.Database
    Set rs = mDb.TableDefs("GUID").OpenRecordset(dbOpenDynaset)

        '// create a queryDef with a fake query (will be replaced later)
    Set mQd = mDb.CreateQueryDef(rs("ID"), "SELECT ID FROM GUID;")
End Sub

Private Sub Class_Terminate()
    mDb.QueryDefs.Delete mQd.Name
End Sub

    '// Function acting as a constructor
Public Sub Construct(ByVal sql As String)
    mQd.sql = sql
End Sub

Public Property Get Parameters() As DAO.Parameters
    Set Parameters = mQd.Parameters
End SUb

Public Function OpenRecorset(Optional RecordsetType As DAO.RecordsetTypeEnum = dbOpenSnapshot) As DAO.Recordset
    Set OpenRecordset = mQd.OpenRecordset(RecordsetType)
End Function

Public Sub Execute(Optional ByVal options As DAO.RecordsetOptionEnum = dbFailOnError)
    mQd.Execute options
End Sub

Open in new window

    '// Factory standard module
Option Explicit

    '// Call this function to instanciate an object
Public Function CreateQuery(ByVal sql As String) As Query
    Set CreateQuery = New Query
End Sub

Open in new window

    '// Sample usage
Option Explicit

Public Sub Main()
    Dim sql As String
    sql = vbNullString
    sql = sql & "SELECT myFirstName, myLastName, myAddress" & VbCrLf
    sql = sql & "FROM myTable;"

        '// Instanciate a Query object automatically create a queryDef with random name
        '// (in fact, a GUID string)
    Dim qry As Query
    Set qry = CreateQuery(sql)

    Dim rs As DAO.Recordset
    Set rs = qry.OpenRecordset
        '// destroy the Query object, automatically delete the associated queryDef
    Set qry = Nothing

    '// random code
End Sub

Open in new window

Your tough ?
Rank: Prodigy

Expert Comment

Jim Dettman (EE MVE)2018-11-30 07:57 AMID: 2445786
Two comments:

1. I would avoid using GUID as a table name.

2. For queries, I'm not sure I'd bother.   If you call CreateQueryDef with a zero length string for the name, you get a temp query def that will disappear automatically when it goes out of scope.

Rank: Sage

Author Comment

Fabrice Lambert2018-11-30 09:35 AMID: 2445802
Hmm, don't focus on queryDefs, it is only a sample. Think generic.
I'm thinking about doing the same things with Excel, Outlook, Word objects ect ...
Rank: Sage

Author Comment

Fabrice Lambert2018-12-03 02:38 AMID: 2446203
Plus, from my tests, an Anonymous queryDef does not support parameters (the parameters collection remain empty).