Norfolk Databases

Visual Basic for Applications is an extremely useful tool when used in Microsoft Office Access, but one of its weaknesses is that it is not totally object oriented. Thus when referring in VBA to tables, queries and fields in such tables, queries and in recordsets based on tables and queries, it is not normally possible to refer to them as objects, but rather as members of a collection. One result of this is that Intellisense is not available. Thus to refer to a table called "MyTable" it is necessary to use code such as

1)

Set tdf = db.TableDefs("MyTable)

where tdf has been declared as a DAO.TableDef and db as a DAO.Database (and then set to be the CurrentDb). If the name of the table is misspelt or is later changed, this error is not caught when compiling the VBA, but only when the application is being run. Sometimes the error will slip through testing and it is only when a customer uses it that it comes to light. Cue embarrassment...

This lack of object orientation also causes problems when using a DAO recordset to modify data (it isn't always possible to do it directly with SQL which is the preferred method). Thus code such as the following is sometimes required (rst is a DAO.Recordset)

2) where we have a DAO.Recordset opened on a query called, say, "MyQuery"

rst.Edit

  rst("MyField1") = "Harry"

  rst("MyField2") = 23

  rst("MyField3") = "Truman"

rst.Update

Again, misspellings can cause problems.

Yet another problem is the amount of code that is required. When setting up a temporary query, code such as

3)

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set qdf = db.QueryDefs("MyQuery")
For Each prm In qdf.Parameters
  prm.Value = Eval(prm.Name)
Next prm

Set rst = qdf.OpenRecordset()

rst.AddNew

  rst("MyField1") = "My New Value"

  rst("MyField2") = 23

  ....

We have created an add-in, called SMOG (Standard Module Object Generation) for use with Microsoft Office Access which enables developers to treat tables, queries and fields as objects. Thus the above code can be replaced by the following

1)

Set tdf = MyTable.Table

2)

With MyQuery

  rst.Edit

    rst(.MyField1.Name) = "Harry"

    rst(.MyField2.Name) = 23

    rst(.MyField3.Name) = "Truman"

  rst.Update

End With

3) SMOG, by default, automatically calculates the parameters of a query (if the parameter value is obtainable from the paramater name, e.g. prm.Value = Eval(prm.name)) so there is no need to manually do it as before.

Dim rst As DAO.Recordset

  With MyQuery

    Set rst = .Query.OpenRecordset

    rst.AddNew

      rst(.MyField1.Name) = "My New Value"

      rst(.MyField2.Name) = 23

            ....

The add-in, along with some examples, can be downloaded directly from here.