I'm creating an access database to keep track of the status of data closeout for the year 2021, from the 50 US states. I've made the states (Alabama through Wyoming) the primary key. I've also created forms for the corresponding tables for easy access, because I want to be able to look at states data from a form without having to look through a table, which is messier and more prone to error.
My question is: is it possible to set up the database (for both the tables and forms) so that no additional records can be added (I just want the 50 states), but also none can be deleted? I want to be able to enter and update the records themselves, but I don't want any of the state records to disappear, be renamed, or have additional records be added.
CodePudding user response：
You can also use MsAccess Data-Driven Macros; in Datasheet view, use the Table group, and Click on 'BeforeChange', and add steps to the macro:
This example prevents 'inserts'.
Not rock-solid, of course; the user can delete the macro, and proceeds to insert. Microsoft Doco for MsAccess Data Driven Macros
CodePudding user response：
The Data Macro from the first answer will prevent deletion.
A ValidationRule including the 50 states will prevent adding records and changing the state names.
State field must also have these properties:
- Required=Yes
- AllowZeroLength=No
- Indexed with "Yes (No Duplicates)".
- ValidationRule for State field see below
In ("Alabama", "Alaska", "Arizona", "Arkansas", "California", "Colorado", "Connecticut", "Delaware", "Florida", "Georgia", "Idaho", "Hawaii", "Illinois", "Indiana", "Iowa", "Kansas", "Kentucky", "Louisiana", "Maine", "Maryland", "Massachusetts", "Michigan", "Minnesota", "Mississippi", "Missouri", "Montana", "Nebraska", "Nevada", "New Hampshire", "New Jersey", "New Mexico", "New York", "North Carolina", "North Dakota", "Ohio", "Oklahoma", "Oregon", "Pennsylvania", "Rhode Island", "South Carolina", "South Dakota", "Tennessee", "Texas", "Utah", "Vermont", "Virginia", "Washington", "West Virginia", "Wisconsin", "Wyoming")
CodePudding user response：
As others have noted you can use data macros on the table to prevent deletion of records, insertion of new records, or changes to specific fields. You may also want to consider simply hiding the table by right clicking it in the navigation pane, selecting properties, then checking the 'Hidden' textbox. You might also consider taking the 'lookup' tables like this and placing them in a separate 'lookup' database, linking to these tables from your current database, and then setting the 'read only' flag on the lookup database .accdb file to true. You will still be able to read the linked tables from your current database but you won't be able to change them.