The first rule of developing Access applications is to split your front-end user interface from the backend data.
Ground Rules
That said, there are situations where you may want to keep some limited table data in your front-end application. Let’s establish some ground rules first. If the following statements are all true, I give you permission to store the data in your front-end file:
- The data is read-only
- The data cannot be joined to back-end production tables
- There are fewer than 500 records in the table
- The data rarely (if ever) changes
Acceptable Examples
Here are a couple of acceptable examples of storing local data in your front-end data file. These are examples from my own applications that have been in place for years. If I had it to do over again, I would do it the same way.
List of data validity checks
I wrote recently about creating queries to act as data validity checks. In one of my applications, I have created dozens of such queries over the years. To generalize the process of adding new checks, I have a local table with three fields:
- QueryDef name
- Description of the validity check
- IsActive Yes/No field
This table populates a combo box. The user can run all the validity checks at once (in which case the program loops through the local table), or they can choose a single validity check from the combo box to run one at a time.
I integrate my AutoFitDatasheet routine to make the process of adding a new validity check nearly frictionless.
Custom report builder fields
In a couple of our applications, we have provided a custom report builder. The way it works is that the user picks several fields to include on the report. Some of these report fields are simply table fields. Others, though, may include some rather complex calculations.
Each custom control has a basic type (date, number, or text); its own formatting (number format, text alignment, etc.); and its own Control Source (field name, expression, etc.).
This table of ReportControls is truly an extension of the application’s business logic. The nature of the information is also well-suited to being stored in table form. This type of data is ideal for storing in a front-end local table.
Unacceptable Examples
These are examples of types of data that I personally have stored as local table data, only to regret the decision later in the project. In some cases, I migrated the data from the front-end to the back-end. In other cases, the cost to fix the mistake was too great, and I had to bear the emotional pain of regret and self-loathing that metastasized from my poor decision.
I hope to spare you the same burden.
“Temporary” working user tables
In one of our applications, we had a process that took one of our end users several hours to complete. The nature of the work was such that the user could not “finalize” it until she finished the entire process.
For years, we used a local table in the front end to store the user’s temporary data. This worked great until one day the user did not complete her work during a single session. That alone would not have been a problem, but we had also released an updated front-end file.
She logged in the next morning, received the automatic front-end update, and–POOF!–her hours of work from the day before were gone.
She was not, as it turned out, happy about this outcome.
Combo box row sources
One practice that I followed for a long time was to store small lookup tables in my front-end file.
For example, such a table might look like this:
