ID vs Code

There are cases where a table’s primary key is referred to often in the code. In fact, it might have some special meanings in code.

It is highly encouraged to avoid the use of IDENTITY or AutoNumber (or any auto-incrementing schema) for those keys because it is also usually desirable to create a VBA Enum that corresponds to the table.

If you have a programming logic in your application that depends on a certain value (e.g. a status for example), then the identifier must be stable in the code. An autonumbering column is not a stable solution because a new number gets generated when you delete & reinsert the row. For that reason, it is appropriate to use a code instead. Furthermore, you should not allow the end users of the application to insert or delete rows to the table. You may optionally allow the users to edit certain columns (but not the code itself).

If they need to be able to create and delete new records in the table, then that table cannot be used as a code table. Therefore, the alternative is to create additional columns. As an example, you can create a Closed bit column on a status table which can be then used for the system’s use to enforce a predefined logic.

In either cases, both requires programming on your part, so it cannot be entirely left up to the users to modify the table without updating the corresponding application logic. It is recommended that you maintain a module that corresponds to a code table so that you have a common set of functions for using the table’s values to enforce certain behaviors in the application.