Tag: MSAccess

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 sub

The 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.

Option Compare Database
Option Explicit

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 Sub

Form 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 Sub

txtEmailDynamic_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 Sub

This 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.