[AccessD] Elegant Solution?

Vlad,

If you're interested, I have developed a class called clsDAOLib that wraps
the most common DAO functions.

It handles setting up and shutting down the workspace and database
automatically for you. It handles parameter queries, and running groups of
queries in a transaction. It has full error handling. You can use it as a
standalone global object, or locally within another class or subroutine.

It isn't fancy, but it is mature, well-tested code that I have had around
for years, and have used in Access 2000, Excel and Project.

Code samples:

* Open a recordset from a query:
    Set rsTree = gDAO.OpenQueryDef("qryFrmProjBOMTree")

* Open a recordset from a parameter query:
    gDAO.AddQryParm "PMStatusID", lPMStatusID
    gDAO.AddQryParm "BOMID", lBOMID
    Set rRs = gDAO.OpenQueryDef("qryBOMPMStatus", dbOpenForwardOnly)

* Execute a parameter query and return the records affected:
    gDAO.AddQryParm "PMReleaseDate", dtPMReleaseDate
    lCt = gDAO.ExecuteQueryDef("qryBOMPMRelease", dbFailOnError +
dbSeeChanges)

* Open a recordset from a SQL string:
    Set rRs = gDAO.OpenSQL(sSQL, dbOpenForwardOnly)

* Execute a SQL string:
    gDAO.ExecuteSQL sSQL, dbFailOnError + dbSeeChanges

* Execute a batch of SQL statements within a transaction:
    With gDAO
        .AddSQL sSQLCommoditySaveDeletedPart
        .AddSQL sSQLCommodityPartDelete
        .AddSQL sSQLRuntimeSaveDeleted
        .AddSQL sSQLRuntimeDelete
        .ExecuteSQLBatch Options:=dbFailOnError + dbSeeChanges
    End With

If anyone is interested, please email me offline for the code, and short
instructions on use.

-Ken


-----Original Message-----
From: ACTEBS [mailto:actebs at actebs.com.au]
Sent: Friday, September 24, 2004 2:13 AM
To: access group
Subject: [AccessD] Elegant Solution?


Hi Everyone,

Does anyone have any code or such that handles the following elegantly:

Dim dbs as DAO.Database
Dim rst as DAO.Recordset

Set dbs etc etc

Do whatever here

dbs.close
dbs = nothing etc etc

I am tired of having do the above to open database connections and
recordsets in the above manner in every Sub or Function. Surely there's
a  more elegant method that someone has come up with here you just parse
the SQL Statement through or something...

Any suggestions or links will be greatly appreciated...

Regards
Vlad