SMOG is an Access add-in which makes the VBA used in Microsoft (Office) Access more object-oriented and works in versions of Access 2000 onwards. Its main function is the replacement of collections by objects through automatic generation of VBA code to create objects, which are then stored in Standard Modules. Thus instead of, say, referring to a table's name thus (the "standard" way)
SMOG enables it to be referred to thus (the "SMOG way").
This apparently innocuous change immediately provides two important gains:
* We can use Intellisense.
* We have compile-time checking that the table is correctly referred to.
SMOG provides object-oriented access to tables, queries, forms and reports, including the names of all fields in tables and saved queries. An example of such code is:
Other advantages provided by SMOG include:
1. It can automatically evaluate parameters for queries, e.g. where a parameter equates to the name of a control on a form, and thus there is no need to provide values for queries prior to opening recordsets based on them.
2. It provides a means for recording "persisted public variables" both local to the front end file and/or in the back end file (i.e. to make them available to all users in a multi-user database)
3. It provides an object-oriented source for Access application options, i.e. those options settable via SetOptions and retrievable via GetOptions, such as "Auto Compact" and "Themed Form Controls", whose names you can never remember and which you have to search for on the Web just to find out what they are.
Let's look at these three advantages in turn
1. "Standard" code for opening a recordset based on a parameter query whose parameters are the value of various controls on a form might be
Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Dim prm As DAO.Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("qselManufacturerThis")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Set qdf = Nothing
Set db = Nothing
SMOG code would be
Note that if you wish to manually provide the values of the parameters, you will need to first set the optional parameter thus
SMOG also provides a QueryTemp object for easy generation of temporary querydefs.
2. Persisted public variables can be used in a number of ways. They can be thought of as database constants, e.g. you may wish to record a "database author" for use in a copyright notice or you may wish to use them as variables which persist between Access sessions, replacing tables which consist of just one record. Thus, instead of having to write code to create a database property called "Author", SMOG allows you to just add a record in the USysPPVs or USysPPVsRemote table and retrieve that record with VBA as simple as
or set it with
Author = "Freddie Flintoff"
Note that you can store these variables either in your front end file, so that they can be user-specific (in the USysPPVs table), or in the shared back end file (in the USysPPVsRemote table) so that they can be shared amongst all users in a multi-user scenario.
3. Access provides a whole raft of Application Options, which can be set through VBA code, but finding out the name of the various options is difficult as they do not appear in the Object Browser and it is necessary to search the Web for details. Confusingly the name of the option is not always the same as the string to be entered when setting or retrieving the option. Thus in Access 2007 if you wish to set the "Picture Property Storage Format" option so images are converted to Bitmaps, e.g. to preserve compatibility with previous version of Access, you would normally need to use code similar to the following:
SetOption "Picture Property Storage Format", 1
SMOG allows you to automatically create a standard module containing details of Access'
options and to set and retrieve them in an object-oriented manner thus
Since we can use Intellisense, the developer is automatically given the choice of
ConvertAllToBitmaps or PreserveSourceImageFormat.
I've been using this method for 8 months or so now and have found it reduces the complexity of my code significantly.
PS a new version of SMOG has been made available for download. Changes to it include:
1. Correction of a couple of errors in the SetOption part.
2. Ease of updating SMOG.
3. Now specifically allows you to set up SMOG to work with Access 2010, Windows 7 and Windows 2003 Server (previous versions of SMOG worked anyway, but you had to pretend to be using Access 2007, Windows Vista and Windows XP respectively).
To update your SMOG add-in, you will need to first find where Access stores the SMOG2.mda file. If your current version is earlier than 1.039 (the new version available from here at UtterAccess is 1.041, you will need to go into the Access add-in manager (as if to install a new add-in) and click the “Add New...” button. You will then be presented with a dialog box and you can copy the path from the top dropdown list. As an example, on my PC the path is “C:UsersAdmin2AppDataRoamingMicrosoftAddIns”. It is into this folder that you need to extract the SMOG.mda in the zipped download file.
Note that in this latest version of SMOG (1.041), there is now a button on the “Modifications History” tab which will create a shortcut to the add-ins folder on your desktop. Any future updates to SMOG can be “installed” on top of a previous version by dropping the new version of SMOG2.mda onto that shortcut.
Update from Alan (November 19/09)
I have made another small modification to this add-in which you have on the Code Archive forum. When a form or report is selected, SMOG now creates a module for it (as happens if you manually set HasModule to Yes) if it does not already have one so that users can refer to properties of the form in the manner of
Form_frmMyForm.AllowEdits = True
which means the user can use Intellisense and has compile-time checking available.
The latest version is available from www.premierdatatech.co.UK/SMOG.aspx .
If you have questions about this submission, Alan has indicated that he would be happy to receive your PM.
post and attachment updated Sept 7/09
Edited by: argeedblu on Mon Aug 3 19:01:09 EDT 2009.
Edited by: argeedblu on Wed Aug 5 10:36:20 EDT 2009.
Edited by: argeedblu on Mon Sep 7 17:13:24 EDT 2009.
Edited by: argeedblu on Mon Sep 7 17:15:01 EDT 2009.
Edited by: argeedblu on Thu Nov 19 5:38:52 EST 2009.
Edited by: argeedblu on Fri Nov 27 17:45:13 EST 2009.