How to make a table read-only in Access?

Question

I have made up Tracker form in Access 2013 in which end user update their daily routine tasks. I want to keep the table as read-only so that no one can make any unauthorized changes in the existing data.

Is there any way to do that in Access?

Fastidious

Answer 1

One solution would be to

• move the reference table into a separate database file,
• make that file read-only (e.g., by using Windows permissions on the file), and
• use a Linked Table in the main database to access the reference table.

Unbelieving

when I make the reference file as read only. It restricts the user by the entering the data using form. – Fastidious

@Fastidious What is the Record Source of the form? If the Record Source is the just the read-only table then obviously you cannot make changes. If the form is bound to a query that includes fields from the read-only table and other fields from some other (writable) table then you should be able to edit those other fields (and be prevented from editing the fields from the read-only table). – Unbelieving

Keep in mind that you cannot create relations over multiple accdb files – Gallagher

Answer 2

Use a query in place of the table, and change its Recordset Type property to Snapshot.
If you want to avoid users opening the table itself, move the table to another database and change the Source property of the table to the path of the other database. In SQL it gives something slike:

SELECT * FROM myTable IN 'f:\test\hidden.mdb'

Chemush

Answer 3

AFAIK you cannot make a table read only, but you can do a number of things to lock down the database so that the user only has access to forms that are read only.

In Options deselect:

• Use Access Special Keys
• Display Navigation Pane
• Allow Full menus
• Allow Default Shortcut Menus

In the form, set the following properties to No:

• Allow Additions
• Allow Deletions
• Allow edits

Literate

Thanks for the advice. It really helpful. But anyone who knows just a bit about access. Can easily make changes which results in serious data security issues. Do you know to any other way to put password on tables or using vba code? – Fastidious

see #15387361 – Literate

If you disable the shift bypass key, it is next to impossible to get in. – Literate

@Fastidious if you need that level of security Access is not the tool for you. Access is great when all your users are on the same team. i.e. nobody would do something malicious. You use access to do correct data entry but not to stop hackers. – Terry