> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Enforcing a Single-Row Table in MS Access and SQL Server
- URL: https://nolongerset.com/single-row-table-how-to/
- Published: 2021-07-28T21:22:11.000Z
- Updated: 2026-05-08T13:03:24.000Z
- Description: 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.
- Author: Mike Wolfe
- Tags: Intermediate, SQL Server, #Import 2026-05-20 02:59

## 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](https://stackoverflow.com/q/7933518/154439) 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](https://docs.microsoft.com/en-us/office/vba/api/Access.CurrentProject.Connection). Here is SO user [HansUp](https://stackoverflow.com/users/77335/hansup)'s excellent [solution](https://stackoverflow.com/a/7933927/154439) to the problem:

```vba
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](https://www.oracletutorial.com/oracle-basics/oracle-dual-table/).

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](https://stackoverflow.com/a/3967446/154439) from user [Damien\_The\_Unbeliever](https://stackoverflow.com/users/15498/damien-the-unbeliever):

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

---

### External references

[Table-less UNION query in MS Access (Jet/ACE)This works as expected: SELECT “Mike” AS FName This fails with the error “Query input must contain at least one table or query”: SELECT “Mike” AS FNameUNION ALLSELECT “John” AS FName Is this ...![](https://cdn.sstatic.net/Sites/stackoverflow/Img/apple-touch-icon.png?v=c78bd457575a)Stack Overflowmwolfe02![](https://cdn.sstatic.net/Sites/stackoverflow/Img/apple-touch-icon@2.png?v=73d79a89bded)](https://stackoverflow.com/q/7933518/154439)

[CurrentProject.Connection property (Access)Microsoft Docso365devx](https://docs.microsoft.com/en-us/office/vba/api/Access.CurrentProject.Connection)

[User HansUpStack Overflow | The World’s Largest Online Community for Developers![](https://cdn.sstatic.net/Sites/stackoverflow/Img/apple-touch-icon.png?v=c78bd457575a)Stack Overflow![](https://cdn.sstatic.net/Sites/stackoverflow/Img/apple-touch-icon@2.png?v=73d79a89bded)](https://stackoverflow.com/users/77335/hansup)

[Oracle DUAL Table: What is DUAL Table in OracleThis tutorial introduces you to Oracle DUAL table which is a special table used for evaluating expressions or calling functions![](https://www.oracletutorial.com/wp-content/uploads/2018/03/cropped-favicon-7-192x192.png)Oracle Tutorial![](https://www.oracletutorial.com/wp-content/uploads/2017/12/Oracle-DUAL-Table.png)](https://www.oracletutorial.com/oracle-basics/oracle-dual-table/)

[User Damien\_The\_UnbelieverStack Overflow | The World’s Largest Online Community for Developers![](https://cdn.sstatic.net/Sites/stackoverflow/Img/apple-touch-icon.png?v=c78bd457575a)Stack Overflow![](https://cdn.sstatic.net/Sites/stackoverflow/Img/apple-touch-icon@2.png?v=73d79a89bded)](https://stackoverflow.com/users/15498/damien-the-unbeliever)

[SQL Server: how to constrain a table to contain a single row?I want to store a single row in a configuration table for my application. I would like to enforce that this table can contain only one row. What is the simplest way to enforce the single row![](https://cdn.sstatic.net/Sites/stackoverflow/Img/apple-touch-icon.png?v=c78bd457575a)Stack OverflowMartin![](https://cdn.sstatic.net/Sites/stackoverflow/Img/apple-touch-icon@2.png?v=73d79a89bded)](https://stackoverflow.com/a/3967446/154439)

*Image by [RENE RAUSCHENBERGER](https://pixabay.com/users/rauschenberger-4614580/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=3848628) from [Pixabay](https://pixabay.com/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=3848628)*