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.
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
CREATE TABLE T1( Lock char(1) not null, /* Other columns */, constraint PK_T1 PRIMARY KEY (Lock), constraint CK_T1_Locked CHECK (Lock='X') )