Reader Q&A: Single-Row Tables

Longtime reader Mark Burns emailed me with a few questions about single-row tables and related concepts (shared with permission, lightly edited for formatting and length):

Some Programming Style Questions

I want to ask a few questions concerning a few concepts I’ve used or inherited in some Access applications.

The first of these is single-row data tables (Access-only tables for now – we can discus SQL Server/other-BE datastores and their alternate behaviors another time).

1. Do you use or recommend them at all?  If not, why not? If so, why?
2. Assuming you have used them (at least once upon a time), how did you treat the data in them? By that I mean, did you automatically trust it, or treat it with the suspicion that since it’s an “unsecured” data table, the contents could otherwise easily “get messed with”?
3. …that second question leads me to this more nagging question: I’d once heard a programmers’ version of “if you don’t want to know the answer to a question, don’t ask it.” Which amounts in this instance to:

When I read a date value in from my single-record data table, can I trust it in the code to be what I expected it to be….and if it isn’t, then what do I do?

-OR, perhaps better stated-

DIM vDte as variant, y as integer

'single-record-table data lookup
vDte = DLookup("SessionDate", "t_CTL", "ID = 1")  'dLookup() CAN return NULL
If IsDate(vDte) Then
    y = Month(vDte)   'the correct SYSTEM Date
Else
    '...now what?!
End If

…and so, at last, my question is: as part of your programming style…do you ask this kind of question (if only as a self-defensive measure)…or do you have better ways to avoid needing to make a habit of it?

My Responses

Single-Row Tables

1. Do you use or recommend them at all?  If not, why not? If so, why?

I do use and recommend single-row tables.

Single-Row Tables in Back-End Databases

When stored in a back-end database, they are a great option for storing configuration settings that:

• Are client/tenant-specific
• Apply to all users

Here's a short list of the types of values that I would store in such a table to give you an idea of how I would use them in the real world:

• An organization's preferred date/time formats for reporting
• Network folder location for binary files (images, PDFs, etc.)
• Company logo

Single-Row Tables in Front-End Application File

While I've occasionally used single-row tables in front-end files, global constants are generally a better alternative.  A single-row table in the front end would hold settings that:

• Apply to all clients/tenants
• Apply to all users
• Cannot be modified by users

The types of values I would store in a front-end single-row table are those that don't work as well as a global constant.  For example:

• An embedded image used on multiple forms/reports
• Values I want to be able to reference directly from a form or report control (e.g., via a DLookup())

As I think about it, I know that I support applications with single-row front-end tables, but I'm having trouble remembering the last time I created a single-row front-end table.  In other words, I would not recommend using such a table in an Access application front-end (with the caveat that I may be overlooking some use case where it would make sense).  Not because they are inherently bad, but because there are better alternatives.

2. Assuming you have used them (at least once upon a time), how did you treat the data in them? By that I mean, did you automatically trust it, or treat it with the suspicion that since it’s an “unsecured” data table, the contents could otherwise easily “get messed with”?

Trusting Front-End Single-Row Table Data?

I automatically trust the data in front-end single-row tables because the users have no direct access to them.  

End users always launch my applications in Runtime mode.  I never expose raw tables to users.  I don't provide forms bound to front-end tables.  Every program update will overwrite the contents of a front-end table, so it never makes sense to let users modify the data in them.

Trusting Back-End Single-Row Table Data?

While it's not as absolute as front-end tables, I generally trust the data in single-row back-end tables for the following reasons:

• I enforce that the table can have one and only one row (not zero, not two, etc.)
• I use the narrowest data type possible (e.g., if it's a date value, I don't store it as a string)
• I pay close attention to whether a field is optional or required and act accordingly
• I never use datasheet views that allow direct editing of the data
• I provide guardrails to prevent well-meaning users from unintentionally storing bad data (e.g., checking that dates fall within a reasonable range, etc.)

Could a malicious user enter bad data on purpose?  Sure.  But Access really isn't set up to mitigate attacks from malicious users, especially when backend data is stored in Access files.  Honestly, If I had concerns about malicious users, I would recommend my client move to a more secure alternative than Access (a SQL Server back-end, at minimum).

3. …that second question leads me to this more nagging question: I’d once heard a programmers’ version of “if you don’t want to know the answer to a question, don’t ask it.” ... as part of your programming style…do you ask this kind of question (if only as a self-defensive measure)…or do you have better ways to avoid needing to make a habit of it?

Philosophically, I believe in programming defenses in depth.

At a practical level, I like to use guard clauses at the top of my procedures to validate inputs.  If there is a problem, I immediately either (A) throw an error or (B) exit the routine (such as with my SaveFails function).  

I hope that answers your questions, Mark.  If anyone else has any burning Access or VBA questions, don't be afraid to pass them along.  And don't worry, I'll never post your name or question without getting your permission first.

Cover image created with Microsoft Designer