Storing Local Table Data in a Front-End Access Application

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:

The table above would be used to populate a combo box control. The StatusID column was bound to a field on the Widget table, named Widget.StatusID.

I favored this approach especially if the Widget’s StatusID value impacted my application’s business logic. In other words, if I were treating “New” widgets fundamentally different than how I was treating “Used” widgets, I preferred to store that Status lookup table within the front-end database file.

I did this because I felt that anything that affected business logic belonged in the front-end file. This was especially the case before I brought my back-end data schemas under version control.

Now, I still think those are valid points. The problem in this case is one of performance.

If I want to join the linked Widget table to the local Status lookup table, Access makes that easy. Of course, just because it’s easy does not mean it’s a good idea. “Joining” two tables that exist in different backend databases will kill the performance of whatever form or report relies on such a join.

Prefilled reason tables

This one differs from the previous example in that there is no foreign key-type connection between the two tables. Rather, this is simply a list of common entries in a free-form notes field. The table is used as the RowSource for a combo box where LimitToList is set to No and AutoExpand is set to Yes.

Consider the following list of excuses:

A homework grading application might have a “Remarks” field where the teacher could enter the student’s excuse for why their homework is missing. While it’s unrealistic to provide every excuse, you could save the teacher some typing by providing the most common ones.

The problem here is that this list might change over time. Adding, removing, or editing a prefilled response should not require a program change. But if those excuses are embedded in a local table, that’s exactly what will happen.

Referenced articles

Split Your Microsoft Access Applications…Or Else

“Ghostbusters” has some important lessons to teach us about Access application development.