Protect Data in Tables and Queries
This video is the first in a 2-part series. It demonstrates a simple method of protecting your data by prevent users editing tables and queries. This is designed to block knowledgeable users from meddling with your database.
In this example, tables & queries are made read only. This is made possible by the fact that tables & queries behave in many ways like datasheet forms with similar code used to manage their properties.
Related article with example app and all code: https://isladogs.co.uk/protect-tables...
In the second article, users cannot view the data in tables and queries, nor copy or export objects to another application. They will also be unable the code used in the visual basic editor - access to the VBE is blocked even in an ACCDB file. That article with example app and all code is at: https://isladogs.co.uk/lock-down-data... and the video will be available to view at • Lock Down Database Objects on Sun 28 April
Times:
0:00 Intro
0:21 Outline of the approach used - make tables / queries read only by altering the properties of the associated datasheet form
1:41 DEMO - standard database security measures have deliberately not been applied for the purpose of this video
2:32 All forms are editable in this example but code could be applied to limit individual user permissions
2:57 All tables and queries are read only. Can be done without using a constraint
4:21 How the app works - autoexec macro opens a hidden form frmHide which runs in the background until the app is closed
5:06 Form frmHide has a timer event which runs a function SetTableQueryReadOnly tests for an active datasheet every 0.5 seconds.
It then checks whether the datasheet is a table or query and, if so, sets edits/addirtions/deletions/filters false (making it read only). If its a datasheet form, the code is bypassed
7:30 Users can still change to design view, export to Excel or copy data to e.g. Notepad. Disable default shortcut menus in Access Options to prevent this
8:15 Thanks for watching. Please like, leave a comment & subscribe.