Motivation
This document defines a set of guidelines for developers, creating MS Access solutions. As MS Access is intended to be used by IT professionals as well as by non professional end users likewise, it includes functionality for both audiences which should not be mixed up.
From professional point of view, not every convenience feature, provided by MS Access, should be used, when a professional, scalable and reliable MS Access solution is intended to be developed. Following the guidelines in this document prevents developers from starting using those convenience features, where they only later will recognize, that this convenience is going to mess up a clean software architecture and database design by and by.
However, following these rules thoughtlessly won’t automatically lead to clean and reliable software. Each guideline may be violated against, either in individual cases or for the whole project. It makes sense to document the violations in proper manner.
The following rules do only cover guidelines specific to MS Access. They do not cover any general database design rules, like normalization, transactions, ID generation or such. These guidelines also do not cover general programming guideline topics, like naming conventions, indentation of code, etc. All such rules are discussed in many other articles already available online (e.g. refer to [1]).
Rules
1. Tool configuration rules
Rule 1.1: Develop with targeted MS Office version
Explanation
Although testing in target-like environment seems to be enough, to guarantee the MS Access solution working at customers system, it should be considered, that finding (or even avoiding) problem during development phase will be much cheaper than finding them not before testing phase.
So, use the targeted versions of MS Windows, MS Access and MS Office (if needed) for development, on a system where newer versions never have been installed.
Risk
Although a new version of MS Access may increase the convenience during development time, it is better for developer to go back to version of MS Access the customer solution is targeted for. New MS Access versions introduce new features and functionality, which may be used by developer without any notice. Even newer versions of MS Office combined with an older MS Access (like MS Office 2010 combined with MS Access 2007) can create difficulties as MS Access project may refer to newer DLLs introduced by new MS Office. Also notice that uninstalling MS Office may not remove each and every of the new DLLs from the system as it should do.
Rule 1.2: Use “Name AutoCorrect” feature with care
Explanation
Using the Name AutoCorrect feature during development in MS Access can really help developers in prevention of failures and to get to know the relationships of all objects (tables, queries, forms and reports) among themselves.
But it is very important for the developer to know how the Name AutoCorrect functionality works in background as it sometimes might behave different than one expects (see also [2]). When knowing the backgrounds, developer then needs to follow the following simple rules to get the expected result out of this functionality:
Know the limits of Name AutoCorrect feature.
Consider that Name AutoCorrect feature does not work on macros, data access pages, VBA code, union queries, pass through queries and data definition queries. It also does not populate changes from tables in connected backend files to frontend files.
Open each MS Access object after activating Name AutoCorrect.
When Name AutoCorrect has been enabled for a database on which it was disabled formerly, developer should open and close each individual table, query, form and report, one by another, so that Name AutoCorrect feature can work properly. Name AutoCorrect does its internal recalculation of relationship structure only on opening an MS Access object.
Open each MS Access object after changing to start passing the change to depended objects.
Open and close each table, query, form and report, one after another, after renaming something, to allow the Name AutoCorrect feature to detect the broken references properly.
After changing the name of one object, do not create a new object with same name, before change has been populated to each dependent object.
It fails to detect a broken dependency in a situation like the following:
tabel1 referred by form1table1 renamed to table2- New
table1 created before form1 has been opened
Result: On next time opening form1 the Name AutoCorrect feature cannot detect dependency to table1 as broken and doesn’t correct it to table2.
Solution is, after changing name from table1 to table2, open form1, so that Name AutoCorrect can correct the broken dependency. After that, a new table1 can be created without any concerns.
Risk
The risk of not knowing how Name AutoCorrect feature works in detail and not complying with the rules above is (while Name AutoCorrect is still turned on), that this functionality yields to unintentionally behavior and update or change of something might break relationship where automatic healing were expected instead.
But it is also not a proper solution to completely disable this feature as it can support the development process to prevent introducing new bugs and actively support keeping overview of the overall relationship structure.
2. Architecture rules
Rule 2.1: Separate application in three tiers: data, model and view
Explanation
Even if it is convenient, to connect a form directly to a table, it is highly recommended to not do so. Every report and every form should use the query layer to connect to data. Even VBA code usually should not directly work on tables, but on queries to retrieve or manipulate data.
Additional, queries should serve as an abstraction layer between the data, stored in tables and the view on that data. Different queries to same table implements different views on the same data, e.g. from different viewpoints, different user roles, different access or security level.
Where queries are used this way, there is a chance, that table design changes (e.g. for performance improvements) may only lead to query update without the need to touch reports and forms at all.
Refer also to rule 6.1.
Risk
Accessing the same data from different tiers inside the application will result in more complexity and confusion to the developer than needed. The application will be harder to test, whether automatically or manually. Familiarizing new developers to the project, or even same developer after long time, due to enhancements, will be much more complicated and time consuming.
Exceptions
However, in rare cases there might be necessity for VBA code to directly operate on tables. For example in case of backend update, VBA code (in frontend) may alter the tables and their relationships directly.
Rule 2.2: Divide into frontend and backend files
Explanation
Even if it is only a small MS Access application on only file level without any need to refer to any database server, MS Access application should be split up into two (or more) distinct files. The file or files containing the backend should only contain tables holding data. The file or files containing the frontend should contain any stored SQL statements as well as GUI elements such as forms and reports.
Risk
Deploying an MS Access file containing backend and frontend as once can be risky or disadvantageous in following topics:
- When used by many users at the same time (file is stored at some network location), the one-file-solution will become slow and inefficient to use. Instead, deploy dual file solution in a way, where the one and only backend file is stored at some network location and the frontend files are stored on each user’s computer.
- Updating the GUI after solution has already been gone into productive use, the data, stored in tables, will be lost, or migration is complicated and expensive. On a split up solution, update of GUI is as easy as just deploying the frontend to the user’s computer while still connecting to the “old” backend. Updating the backend can be implemented by altering table structure from new frontend as well without destroying productive data.
3. Table design rules
Rule 3.1: No lookup fields configured in tables
Explanation
Do not use lookup fields for foreign keys in tables itself. Use regular integer ID to refer to primary key in other table instead.
When designing forms or reports, of course, one should make use of record source or row source to present convenient prepared data for foreign key fields to the user.
Risk
In many cases, problem solving during development needs much more time when developer just can see the “looked up” data instead of the actual “low-level-values” in a foreign key field.
In the case, where “looked up” data on foreign key fields is much more helpful than the actual data, use an SQL query instead.
Rule 3.2: Prevent table design to influence GUI implementation
Explanation
Unfortunately, some properties on table design level will later influence properties on forms or reports. In particular, this is
- description of table field, which will later be copied to status bar text of each control representing that field,
- caption of table field, which will later be copied to labels associated with controls representing that field,
- validation text and input mask of table field, which will later be copied to same properties of controls representing that field.
Risk
Although that might look like a comfortable way to automatically set correct captions and status bar texts for each field, this will yield in too much confusion after several renames. Additionally it won’t work where MS Access applications split up into frontend and backend files (see also rule 4).
For describing tables and its fields, use description field in table properties instead.
Rule 3.3: Don’t use AutoNumber as meaningful data
Explanation
Do not use AutoNumber value for any meaningful purpose (such as increasing number or number of rows in table) other than generating an individual, only application internally used value.
The AutoNumber value in MS Access is meant only for automatically generating distinctive numbers. This number will not increment by 1 each time one adds a new record. Also this number may be reorganized or changed on compressing database and other MS Access internal cleanup routines. So one can only rely on the unambiguousness of that number – nothing else.
One can even not restrict the format of that number, to generate e.g. an exactly 6-digit voucher number or something similar.
Also there is not any need to reset the AutoNumber value to start with zero or to any continuously sequential order with no gaps in between. It just must not matter to any user or developer if the three only records in the table have the AutoNumber values 0, 1, 2 or 25, 1034 and 567.
Risk
So, using this number for any additional meaning other than unambiguousness may be really risky and uncontrollable.
Rule 3.4: Enforce referential integrity wherever useful
Explanation
Dependency between individual records must meet the intentional relationship of table design. So this is why referential integrity must be enabled for each dependency between fields where needed.
Risk
With activating referential integrity, MS Access ensures that no records will exist, while that master record, they depend on, has been removed. Developer can even choose the strategy, how to enforce this integrity: Either by denying the deletion of the master record or by deleting all depended records as well when master record will be deleted.
Enforcing this rule on table design level will help developers to not oversee such of these rules during GUI or query design.
Rule 3.5: Implement integrity rules on table design level
Explanation
All the rules, which allow or disallow a field to contain no data, NULL data or zero length strings, can be summarized as integrity rules. Additionally, rules for the integrity of field relationships among tables (enforcing referential integrity, cascading deletion or update).
Make use of all of these rules on every field to configure an overall integrity system on table level. Do not let these rules too relaxed for implementing the integrity system on forms level.
Risk
On forms level, there is high risk for the developer to forget the one or other rule to be enforced or checked on each and every form and control. The risk is too high then, that sooner or later the table may get filled with data containing exceptional values or relationships that is not expected by other forms or reports.
Although a table design and its integrity system may be deployed in a buggy state as well, the risk of getting unstructured data because of user interface bugs is much higher. Especially as one rule on user interface level may involve many forms and controls to be handled in VBA properly whereas on table level each rule is configured at only at only one single point during table design.
And also on interfaces, where data can be delivered by other systems (like imports from Excel, other databases, etc.), integrity rules won’t be checked when implemented only on user interface level. So if the database backend system of MS Access can enforce the rules by itself, it lowers the risk of feeding unstructured data from any channel.
Rule 3.6: Text fields should not allow zero length strings
Explanation
Fields can be configured to accept to contain NULL data. NULL data is an important feature of (not only) MS Access to not force the user to enter data in each and every field. Additionally to NULL data, string fields can be configured to accept “Zero Length Strings” as well. This feature should be disabled. Only use NULL data to allow the user to not enter any data into a field.
Risk
NULL data and “Zero Length Strings” are different content. Handling them via VBA code need usage of different routines for checking or writing these values. So, when accessing string fields of tables per VBA, there is risk, that the developer may forget to handle all these exceptional values.
Exceptions
NULL data and “Zero Length String” values are not only distinct values but also have its very own meaning. NULL data means something like “Unknown”. This is why a field, which has not been given content yet (or on a new record), contains the NULL value, as the database doesn’t know anything about its content so far. In contrast, a “Zero Length String” means something like “known empty”. In this case the database definitely know that this field is empty (e.g. as it is explicitly entered somehow or selected by user).
“Academic degree” may serve as an excellent example here. Whereas it is NULL on a new record or when it is not entered yet (thus the database does not know anything about the academic degree of the current record), it may later be set to Zero Length String (thus the database now knows is should be empty).
If it is really necessary to use both distinct content types for one field, developer might consider using this explicitly. In this case special care should be taken during VBA coding for this (or these) field(s). Also on forms and reports both different values should be explicitly shown to the user somehow and the user should get the ability to set the field(s) to each of these values explicitly.
Rule 3.7: Implement validation rule on table design level
Explanation
Validation rules are also some kind of integrity rules as it enforces rules regarding different fields of the same record. So, this is why validation rules also need to be implemented on table level, like the other integrity rules.
Risk
The risks of implementing validation rule on user interface level rather than on table design level are the same as the risks of not implementing integrity rules on table level (refer to rule 3.5).
Rule 3.8: Don’t implement Input Mask on table level design
Explanation
In contrast to validation rules or integrity rules, input masks are a different story. They only constrain input on user interface level, even if they are configured on table design level. So, do not make use of this constrain here. But make use of it on designing forms instead.
All the more, instead of using input mask to constrain the values, accepted by the table itself, make use of integrity rules, validation rules and allow or disallow empty fields, NULL values and zero length strings.
Risk
Using this feature on table design level will then affect the form design process later. Instead of using it here, developer should take care of the configuration of every control on each form individually. When later the input mask on table level will be changed, the change may not be populated to each and every form than. Even worse, if it nevertheless might be populated, it may overwrite an already changed input mask on form design.
Using the input mask on table design also prevents the developer to input unexpected data on table level to test forms and reports properly.
Input masks will also not be considered, for data coming from other interfaces (such as imports from Excel, text files or other databases). And so unexpected data might be imported into the database and must be handled by forms and reports which may not be tested to work with that data properly then.
Rule 3.9: Tables should be configured to have no Subdatasheet
Explanation
Set the property “Subdatasheetname” of each table to “[None]”. This feature is set to “[Auto]” by default. It is mostly used to show one-to-many relationships to the user.
Risk
One might significantly reduce performance and increasing of size of the database if this property will be kept active. So, if one does not make use of this feature, it should be enabled.
Also, only one (of possible several) relationships of a master to its child tables can be shown here.
Instead of using this automatic feature of MS Access, developer should consider to create form-subforms (resp. report-subreports) to show such relationships to the user.
4. Query and SQL design rules
Rule 4.1: No table-generic SQL statements
Explanation
Do not implement SQL statements which will work on different tables (e.g. with help of parameters). Using parameters for data is okay. But create individual SQL statements for each table at least.
Risk
When SQL statements are too generic, it will become difficult to maintain them on change or bugfixing of tables. Updating generic SQL statements are in risk to be overseen quickly. Instead, when using SQL statements with hard coded reference to tables, MS Access can help identifying all SQL queries, which need to be checked on change of tables.
Rule 4.2: No compound SQL statement
Explanation
Prevent implementing SQL statements in a way (e.g. with several parameters), which solve several tasks at once.
Negative Example: Clone a record and alter field2 (by parameter parForField2) on cloned record at the same time
INSERT into tab(field1, field2, field3)
SELECT tab.field1, parForField2, tab.field3 FROM tab
WHERE tab.ID = parForId;
If atomicity and data consistency is a concern here, use transactions instead of compound SQL statements.
Risk
As soon as complexity of project will increase or more and more changes need to be implemented by customer requirements, one needs each individual function, of the compound statement, on its own finally.
For the above example, divide the two distinct tasks of cloning and updating into two different statements.
Cloning…
INSERT INTO tab(field1, field2, field3)
SELECT tab.field1, tab.field2, tab.field3 FROM tab
WHERE tab.ID = parForId;
…and updating
UPDATE tab
SET tab.field2 = parForField2
WHERE tab.ID = parForId;
5. Form and Report design rules
Rule 5.1: No hidden queries
Explanation
Do not use the possibility to assemble query statements direct in datasource property of forms and reports. Also prevent doing so in VBA code whenever possible. Make use of parameterized queries if needed.
MS Access clearly lists all stored queries inside one panel. With this developers can keep track of all needed queries, their meaning and its sight on the data in convenient way. This is an essential feature, when changing the design of tables.
Risk
On change of table design, developers may oversee the one hidden query or another with the risk of forgetting them to update as well. Also there is a risk, that special requirements to the data model may be forgotten or overseen on altering the table design, when queries are hidden.
Rule 5.2: Prefer to use Ribbon for buttons over buttons (Access 2007 or later)
Explanation
Buttons on forms may alter selection in a grid view even before VBA code for buttons events are called. This occurs because the focus shifts then from grid view to clicked button. Especially multi record selections on subforms are cancelled, when button on mainform is clicked.
This does not happen, if actions are performed on ribbon instead of buttons.
Risk
In order to prevent this behavior by using buttons, however, one has to implement checking and memorizing selection cyclically during a form is in foreground. This is risky, as cyclic update of last selection has to stopped to access this information need properly (e.g. by some kind of semaphore).
Also, implementing and activating the cyclic timer routine of a form, disturbs developing on that forms VBA module during this form is open on GUI.
Rule 5.3: Design distinct forms for adding new records
Explanation
Use distinct forms for adding new and manipulating existing record, when datasheet or gridview is used for manipulation of records.
Risk
Especially when acting on selections of one or multiple rows, developers need to take care of selection of the “new record”.
Rule 5.4: No user interface strings in VBA code
Explanation
When designing forms or reports, developer may want to show messages to the user, such as “Record successfully saved”, “Operation aborted” or “Draft print.” Do not design the message on the user interface by use of a generic message label, which will then be filled by VBA code. Instead put a distinct label for each message on the form or report (e.g. stacked over each other) and enable resp. disable their visibility property via VBA. This way, GUI design and content is separated by business logic.
Risk
It is generally beneficial to separate user interface design from implementation. Redesigning or translating forms or reports, which affect code changes, are of risk to introduce new bugs. Also putting all messages on the GUI directly during design phase immediately shows the result to the developer. When putting an empty label to be filled by VBA later, may show misplaced or too width or short strings only late in development phase.
6. VBA programming rules
Rule 6.1: SQL over VBA
Explanation
On business logic layer implement as much as possible in SQL instead of VBA. Such as deleting, updating or inserting records or changing tables should be implemented as SQL DML query rather than in VBA. It may then be executed via VBA.
Refer also to rule 2.1.
Risk
Queries task is to access the data layer and connect this to view layer. When data layer will be altered (e.g. by adding new features to the application), developers tend to first check the query layer for impacts of that change. Change of VBA code, which relies on specific table design, is harder to implement and developers may forget to update rarely used VBA routines.
Exceptions
However, in rare cases there might be necessity for VBA code to directly operate on tables. For example in case of backend update, VBA code (in frontend) may alter the tables and their relationships directly.
Rule 6.2: GUI independent VBA code
Explanation
Forms and reports as well as their VBA code are hard to undergo automated unit tests. Additionally, there are already frameworks to regressively test individual VBA routines. So it is best practice as development for testability to extract as much functionality as possible out of form or report VBA code module into individual modules or classes. This way, most of written code can be unit tested more easily.
Risk
Implemented functionality may introduce bugs which are uncovered in situations, complicated to reproduce by GUI. Enhancing functionality may also break already working functionality in cases where developer does not recheck the old stuff from time to time at least. So it is not a good choice to develop an MS Access solution without or with too less unit regression tests.
Rule 6.3: Force explicit variable declaration
Explanation
To force the developer to explicitly declare any variable used in VBA code, use
Option Explicit
VBA editor can be configured to automatically add this line of code to top of any newly created module, by setting the “Require variable declaration” in the options dialog of the VBA editor. But remember that this setting only affects newly created modules. Already existing modules are kept untouched and must be changed manually afterwards.
Risk
By not using this option, the VBA developer does not need to declare variables prior to their usage. This seems to be more comfortable at first. But this prevents the compiler to automatically find mistyped variables, which may result in very hard to find bugs.
Declaring a variable at first also defines its datatype and restricts the usage of that variable to that datatype at the same time. So the compiler also is able to find usage, which is not intended for that variable. E.g. putting a surname into a long typed variable, which is intended to take the age, will definitely arise problems when this variable is checked for being greater than 21 later.
Rule 6.4: Declare objects to DAO or ADO class explicitly
Explanation
When using DAO or ADO objects and methods, many objects and methods are named the same. To guarantee unambiguity it is essential to define DAO or ADO objects by explicitly referencing to DAO or ADO library:
Dim rstAdo as ADODB.Recordset
Dim rstDao as DAO.Recordset
Risk
Dim rst as Recordset
When a Recordset object is defined without the reference to their library, like in the above variable, it is unclear, which of the both libraries will be used here. It depends on the configured sequence of the project references to the DAO and ADO library. If both libraries are referenced, that library, which is references at first, will be taken. Now, if the developer works on rst with properties and methods unique to the other library, it will produce a compiler warning which might be hard to understand. Even worse, properties and methods, which exists (by name) in both libraries, may behave different or return different values or datatypes, which may arise even harder to find and fix runtime errors.
Rule 6.5: Prevent using default properties from objects
Explanation
Objects, predefined by Microsoft, come with a preconfigured “default property”. This is one specific property, whose value is returned, when the object is accessed where accessing a property of that object is expected.
Do not make use of accessing that default property. Instead access each property explicitly.
Risk
Example for the ComboBox object:
If Me!cmbClerks = vbNullString Then …
In this example, Me!cmbClerks (Object type) is recognized automatically by the VBA compiler to be not compatible to the vbNullString (String type). This is why, this condition should fail to compile. But instead of throwing a compilation failure, the compiler checks for the “default property”, which is a property value returning a String. In this case, as value can be checked against vbNullString, the condition can be evaluated properly, but maybe not as intended by the developer.
In case of the above example, do following instead:
If Me!cmbClerks.value = vbNullString Then …
or
If Me!cmbClerks Is Nothing Then …
as per intention by the developer.
Here, the first solution checks, if Me!cmbClerks.value contains any value (user has selected or entered a value) and the second checks, if Me!cmbClerks is still uninitialized. Both have a very different meaning. Even worse, not every Object type may have the value property defined as its default property. Different Objects types may have very different defined default properties.
So, to guaranty unambiguousness, do not trust the default property.
Rule 6.6: Don’t return values to MS Access Macros
Explanation
Although, MS Access Macros only can call VBA functions, instead of VBA subroutines, the value returned by the VBA function will be ignored.
So, if a function with a return value needs to be called by a Macro, encapsulate that function call into another VBA function which should then be called by the Macro, instead. This VBA function (although defined with a return value data type) doesn’t need to call the return statement at all. This function should properly react on the return values of its encapsulated function call, e.g. by reporting an error to the user, stop the action, close the database, etc.
Risk
Usually, return values have meaningful information. Ignoring them can lead to unexpected behavior, although it might be only in rare cases. But using a software program many times, one can trust that every “rare situation” will occur once, sooner or later. This is why a proper reaction to a meaningful return value is indispensable.
References
[1] RVBA Coding Conventions: http://www.xoc.net/standards/rvbacc.asp
[2] How Name AutoCorrect Works in Microsoft Access: http://msdn.microsoft.com/en-us/library/aa139941(v=office.10).aspx
Update history
- May 10, 2012.- Initial release. Version 1.0.
About the author
In short: I am software developer for embedded solutions in C.
So what does this have to do with MS Access and VBA programming? First of all, although I do C programming mainly, we use many MS Office Automation tool for many different tasks at work: C code generation, software parameter handling, unit conversion, controller and formula modeling, cost estimation, text processing and beautifying as well as many other process management tools. All these tools are developed by ourselves, mainly in MS Excel and MS Word. This is why, after using VBA on regular basis since many years, I now have thorough understanding of the VBA programming language and the overall MS Office Automation system.
Additionally, as I already have this deep knowledge of VBA programming, I do some freelancing jobs in MS Office programming - mainly MS Excel optimization and MS Access programming, since several years now. And while especially MS Access solutions may grow from a simple quick-and-dirty-tool to a matured database solution with many weeks or months of work, it is important to keep the code, the database and the user interface, their design and their architecture clean and well thought from the very beginning on. However, regarding good database design and GUI design there are many many articles out there in the Internet and also books available, which developers should definitely read before starting bigger projects. But MS Access still has some specialties which need to be considered additionally to build up a good and reliable solution.
This is why I have written this article from the experience of the MS Access development I have done so far.
