To be specific, I want a table that contains a single record, typically this reports version information or status or something like that. Do not allow a second record to be added.
I’ve done this in both Access and SQL Server, here’s the code for SQL:
CREATE TABLE [dbo].[zDBInfo] ( [ID] [tinyint] NOT NULL IDENTITY(1, 1), [AppIsLocked] [bit] NOT NULL, [InfoDate] [datetime2] (0) NOT NULL, [VersionDate] [datetime2] (0) NOT NULL, [DBVersion] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [InfoMsg] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [RowVer] [timestamp] NOT NULL) ON [PRIMARY] GO ALTER TABLE [dbo].[zDBInfo] ADD CONSTRAINT [CK_zDBInfo_ThereCanBeOnlyOne] CHECK (([ID]=(1))) GO ALTER TABLE [dbo].[zDBInfo] ADD CONSTRAINT [PK_zDBInfo] PRIMARY KEY CLUSTERED ([ID]) ON [PRIMARY] GO
The key to this is the constraint. The ID field doesn’t need to be an identity, it can just as not have that parameter as long as you plug in the 1. Users should only have Select permissions on the table so that your application program can see it, when you need to modify any information you do it direct at a higher permission level.
Personally, I like the AppIsLocked field. In my application, the startup menu is actually invisible: it checks if the current user has access to the system and then launches the appropriate menu. But first it checks the AppIsLocked field, and if it is true, doesn’t allow them in to the system at all.