Single Record Table (Access 2002/2003)

Topic

New Reply

Is it possible to create a table and restrict it, so that it will contain only one record.

I think not – but I’d be happy to be proven wrong.

Reply | Quote

Replies

Create a table and add a field ID (Number, Long Integer), plus other fields as needed.
Make ID the Primary Key of the table, so it must be unique.
Enter 1 (the number one) in the Validation Rule property of the ID field, and an appropriately annoying error message in the Validation Text property.
Save the table.
You can now enter one record, with ID = 1.

Reply | Quote

If you also set the default for ID to 1 you don’t even have to type that value into the record (I know that doesn’t save keystrokes for a single table, but might be handy if you use the table as a template for other 1 record tables).

Reply | Quote

thanks guys!

Reply | Quote

OK you have got my inquisitive hat out of the draw now and got me wondering why you need a table with one record and one record in it only?

Reply | Quote

Storing things like Company name and address etc.; settings; maybe even values instead of using global variables in code. Saving values in a one record table allows them to persist from session to session and they do not get lost if your code goes belly-up and resets your globals. I also use it to store the back-end path for re-linking purposes if needed. There are several uses for this technique.

Reply | Quote

Thanks Steve, makes sense

Reply | Quote

In this db I’m using it to store the dates for the current Pay Period. The user changes the pay period and the time sheet is able to calculate when employees have worked more than 44 hours in a week or 88 hours in the pay period.
Only somehow they managed to put in TWO pay periods in the table!

Reply | Quote