Intermediate

Enforcing a Single-Row Table in MS Access and SQL Server

There are many uses for a table with one--and only one--row in it. Using such a table is simpler if you can rely on the sanctity of its one-row-ness.

Mike Wolfe

Jul 28, 2021 • 3 min read

Why Do It At All?

In many of my applications, I have a table with a single row in it. I use these tables for a variety of reasons:

Track the database's current schema version
Maintain client-specific global values (e.g., county name)
Maintain environment-specific global parameters (i.e., dev vs. test vs. prod)
As a dummy table to support table-less UNION queries in Access

Whatever the reason for the table, they all have one important requirement in common: there should be one–and only one–record in the table.

Being confident that there is always exactly one row in the table means you can simplify the process of retrieving data from the table. You don't have to worry about some user (or developer) accidentally adding a second row (or deleting the existing row).

That sounds great, but how can you accomplish that guarantee?

How to do it in Access

There is no way to enforce the single-row constraint in the table design user interface or in code using DAO. The only option is to use ADO.

For local tables, Microsoft Access provides an ADO Connection object for the current project. Here is SO user HansUp's excellent solution to the problem:

Public Sub CreateDualTable()
    Dim strSql As String
    strSql = "CREATE TABLE Dual (id COUNTER CONSTRAINT pkey PRIMARY KEY);"
    Debug.Print strSql
    CurrentProject.Connection.Execute strSql
    strSql = "INSERT INTO Dual (id) VALUES (1);"
    Debug.Print strSql
    CurrentProject.Connection.Execute strSql

    strSql = "ALTER TABLE Dual" & vbNewLine & _
        vbTab & "ADD CONSTRAINT there_can_be_only_one" & vbNewLine & _
        vbTab & "CHECK (" & vbNewLine & _
        vbTab & vbTab & "(SELECT Count(*) FROM Dual) = 1" & vbNewLine & _
        vbTab & vbTab & ");"
    Debug.Print strSql
    CurrentProject.Connection.Execute strSql
End Sub

He uses the table name "Dual" in his code above, which is a reference to the built-in table with the same name in Oracle databases.

For linked Access tables, I believe you'll need to replace the CurrentProject.Connection code in the above snippet with an appropriate ADO connection to the source database.

How to do it in SQL Server

Here's the equivalent SQL Server solution from user Damien_The_Unbeliever:

CREATE TABLE T1(
    Lock char(1) not null,
    /* Other columns */,
    constraint PK_T1 PRIMARY KEY (Lock),
    constraint CK_T1_Locked CHECK (Lock='X')
)