Access seems very convenient. You have visual editors for forms, for data tables, for data relationships, and those things are pretty easy to learn and use. But they also mean that access doesn’t really lend itself to complex, scalable, adaptable applications and especially, to database and office automation applications that might one day need to be moved to a long-term multi-user system.
But it’s very tempting to prototype a solution in Access, for a number of reasons:
- It can all be local (no online storage, no shared resources)
- All the parts are in one platform (the Access interface)
- It saves your work to a single file
But these advantages can and will lead to later problems. This post is a guide to everything I have learned about making an application in Access, mostly for me, so I don’t forget important stuff. But you’re welcome to it. If you have some admin/management tasks that are too complex for Microsoft Power Automate or don’t require, or don’t want, an online solution, this is how to do it.
Application Needs
Any application needs global variables so you can store business rules and business data. You can (and should) store some of that information in a table in the database. Network paths, local paths and URLs should be stored in a Globals TABLE.
Anything else that needs to be stored globally, that will not be modified by a user, can be stored in a class.
Global Class
I usually just call it “Globals,” and it will look something like the following. Inserted directly via the VBA editor, a new Class Module named “Globals”
Option Compare Database
Option Explicit
Public LogPath as String 'path to a debug log file
Public Tokens as new clsTokens 'a class where I store usernames and
'passwords for network locations and websites
Private Sub Class_Initialize()
LogPath = "C:\Logs\MyAccessApp"
Tokens.add "mywebsite", "http://www.mywebsite.com/login", "hwd", "askjh3*(ns"
End subThe class clsTokens is pretty simple. It contains a collection based on another custom object I use in VBA only, called “stringlist.” It’s a wrapper for a key/value collection stored as a delimited string.
ALL my VBA modules begin with the lines
Option Compare Database Option Explicit
This is just good practice.
COMBO BOXES
The options in Combo boxes should always come from a TABLE or QUERY.
You might have a standard list of options but in some places you might also want to include custom values entered manually. That’s why you use a QUERY as the source. NEVER use a value list.
Database
Access is a database tool, but even if you don’t need a database for the purposes of your application, it’s very useful to have one available. However, Access files can bloat very fast, record locking is needlessly complex, and a prototype application can get very messy very quickly, especially if you need to update, improve or change it without having to stop using it for your work.
The solution to all of this is twofold:
#1 SPLIT the database
Splitting your application means you have one Access file that contains the FORMS and QUERIES and all your VBA, and another that contains JUST THE TABLES.
There are some big performance gains to be had this way, but also, you can create multiple versions of the front end and connect them all to the database. I use this mostly so I can have a dev version and a version I’m using for work, but connect them both to real, live data. I know that sounds a bit mad, and it would be for a massive enterprise solution. But I’m talking about single person or small team apps where the usual risks are minimal.
#2 NEVER use a TABLE as a form record source
Don’t use a dynaset RECORDSET either.
Always work with data from a query. This means the data displayed on screen in a form is just a copy of what’s in the database. If you need record locking (a single user application doesn’t. A multi-user database does) I’ve found it’s best to build a semi-manual locking system which functions according to the needs of the users. (Typically this requires adding three fields to every TABLE: “timeStamp”, “locked”, “user”.)
Using a “bound” form (a form dynamically linked to a table (the “row source” property contains the name of a TABLE)) greatly increases record access times.
Form Manager
As soon as you start working this way, you will realize that every form is going to need certain functions – but you might also need different “views” of the same dataset, based on such divergent reasons as being at different stages in a business process or SOP, or because different users have different amounts of screen real-estate. This is where the form manager class becomes really valuable.
Code at the top of the form’s code module:
Option Compare Database
Option Explicit
Private FormMngr As clsFormManager
Private Sub Form_Load()
Set FormMngr = New clsFormManager
Set FormMngr.frm = Me
FormMngr.HNDLForm_Load
End SubForm Manager Class Module
Option Compare Database Option Explicit Private mfrm As Form 'set a reference to the form object Private FSO As FSOWRAPPER 'another custom class for accessing the FileSystemObject's 'functions without having to Dim FSO as FileSystemObject 'in every Method.
This class is divided into sections which I label UNDER the sections (it makes them easier to find, and I find I am less likely to put code in the wrong sections).
'// Variables above this line belong to the form.
Private Sub Class_Initialize()
Set mGlobals = New Globals
Set FSO = New FSOWRAPPER
End Sub
Public Property Get Globals() As Globals
Set Globals = mGlobals
End Property
Public Property Set frm(forForm As Form)
Set mfrm = forForm
End Property
Private Property Get frm() As Form
Set frm = mfrm
End Property
'// properties above this line stay at the top
'// add no methods above this line; properties of the Class only
'// methods above this line belong to the properties of the Class only
This top section is pretty much identical for every form.
Public Sub HNDLForm_Load() setDataSource 'the source may be a "search" form, or a listbox 'so the setDataSource function will be customized to the needs of the form. End Sub Public Sub HNDLForm_Close() 'on close events releaseLocks End Sub '// Above this line, Targets for event handlers for the parent Form object Only
Every form will have LOAD and CLOSE event handlers, if nothing else to handle record locking.
'// methods above this line are targets for FORM CONTROL BUTTONS event handlers '// methods above this line are for the targets of FORM OBJECT VARIABLES DECLARED WITHEVENTS '// methods above this line are targets for OTHER FORM CONTROL event handlers
The three different types are really just my own preference, but it does make them easier to find on big, complex forms. Objects with events are going to include anything with asynchronous callback, which generally needs a lot of debugging, so it’s good to have a dedicated section. MOST of these methods are going to be triggered by COMMAND BUTTON controls, so I group them all. This just keeps them out of the way when looking for the handlers for other control types, which are more likely to need debugging.
So how exactly does FORM EVENT handling work?
Let’s take a text field where an email address is displayed. I want to be able to left-click the field and email the address in the field via GMAIL, but right-click it to get the usual context-menu.
The email is displayed in an unbound text box, with an “event procedure” for the On Mouse Down event that looks like this:
Private Sub txtEmailDynamic_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)
DoCmd.CancelEvent 'prevents any further events from triggering
If Button = acRightButton Then Exit Sub 'polite
If Button = acLeftButton Then
FormMngr.HNDLtxtEmailDynamic_MouseDown
End If
End SubtxtEmailDynamic_MouseDown is the true event handler, so code that manages event propagation MUST be here. But all other code is kept in the Form Manager Class, so HNDLtxtEmailDynamic_MouseDown looks like this:
Public Sub HNDLtxtEmailDynamic_MouseDown()
Dim subj As String
Dim urlGmail As String
subj = "An email from my application"
urlGmail = makeEmailToGmail(frm.email, subj) 'A function that generates a
'URL to pre-fills an GMAIL message. You have to be connected to a
'GMAIL session in your default browser
sendGmail urlGmail 'a sub that opens the default browser and navigates to
'a correctly formatted gmail URL
End SubThis structure enables me to organize the code in my application around the function of individual forms and their views of the data. For example, I can have two identical forms, but one of them suits a smaller screen size or form factor. Or, alternatively, I can have one form, but it loads a different Form Manager class depending on whether it was opened read-only (no record locking, no updates) or edit-mode (changes to the main record) or my personal fave, the main record is read-only, but subforms like an activity lock or checklist can be updated.
Diagram time.
Note: data is queried using SQL in a set of functions for getting data, OR by dynamically building an SQL statement that is pasted into the form’s Row Source property, and then requeried.
I don’t use stored “queries” unless I need a nested query with some sort of Domain Aggregate Function. Access tends to fall over if you try to do those with just a single ACCESSQL statement and with functions.
Also the “query builder” is just as useless as it was in Microsoft Query, nearly 30 years ago.
Complex versions
In more complex versions, I have a separate class that queries data, which itself uses functions from a standard module I call “data_utilities.” This way I can wrap the range of Recordset, Recordset2, DoCmd and other SQL functions that are needed for efficient work with Access data, inside a range of simple-looking function calls.
Main Benefits
This structure has a range of big advantages, in particular for code maintenance.
- Minimal clutter and maximum readability in FORM modules
- no Method duplication between forms that work with the same data
- no duplication of forms for different datasets
- the structure lends itself to readable code that’s more easily refactored
- restructuring the database itself has much less impact
Other Benefits
The simplification of subform management is HUGE. At some point I’ll do a whole post on how I apply the same principle to subforms.
