Version 1.5           Approx 3.3 MB (zipped)                 First Published 21 Mar 2024                 Last Updated 27 Apr 2024


Preamble

This article was prompted by a thread started by user MsAccessNL at Access World Forums: Is the Table you see in Access a Datasheet Form? Yes.

All user interaction should normally be using FORMS.
End users should NEVER have direct access to tables or queries via the navigation pane.

Ideally, the navigation pane should be hidden and other basic security measures applied.
This is usually enough to prevent unintentional damage to database objects.

However, users who know enough to be 'dangerous' will be able to circumvent simple Access security and, in some cases, cause damage by 'meddling'.

A few years ago, I had to deal with repeated support calls from one of my clients due to a member of staff who thought they knew a lot about databases.
Unfortunately, they caused a lot of problems and wasted a lot of my time. It wasn't done maliciously - they just wanted to know how the database worked.
Luckily they didn't know enough to be able to cover their tracks!

It is standard practice to split databases with the Access front end distributed as an ACCDE file. Doing this will protect your code and the design of forms / reports.
Also, the design of linked backend tables cannot be altered from the frontend database.

However, that will not prevent users editing or deleting records in tables or queries. Nor will it prevent users changing the design of any tables in the front end database.

This article describes one very simple method of helping to protect the data in tables and queries.



Downloads

Click to download - ACCDB or 32/64-bit ACCDE (all files are approx 3.3 MB and zipped):

          ProtectTablesQueries_v1.5       ACCDB file

          ProtectTablesQueries_v1.5       32-bit ACCDE file

          ProtectTablesQueries_v1.5       64-bit ACCDE file



Example App

The example app opens to a startup form, frmStart, which outlines what the app does.


You can open 4 tables and 3 queries from the navigation pane. All will be READ ONLY.

There are also 5 continuous and datasheet forms. All except one, frmReadOnly, are editable.



How the Example App Works

The example app shows how tables and queries can be secured by making them READ ONLY. There are TWO ways of achieving this:

a)   Apply a constraint to the table design - see table tblReadOnly.
      This has to be done to each table individually using data definition language (DDL) queries which could be a lot of work.
      Doing this also means the table(s) can NOT be edited using a query or a form!
      Whilst there may be occasions when this is useful, normally this isn't an acceptable solution.

      I will explain how to create contraints in a future article.

b)   Alternatively, code can be used to make table and query datasheets read only without using constraints.
      It uses the fact that when viewing the data in tables and queries, you are actually viewing a datasheet form
      This means that certain events / actions / properties can be controlled using code.
      This is surprisingly easy to do and allows the same data to be made editable in forms as required.

The following code should be placed in a standard module e.g. modReadOnly

CODE:

Public Function SetTableQueryReadOnly()

'Updated v1.5 22/03/2024 to differentiate between tables/queries & forms with the same name
'Thanks to Chris Arnold for this suggestion

Dim frm As Object
Static cfrm As Object

On Error Resume Next

      'the following properties exist in tables and queries
           '. . . but not in datasheet forms (even if the form has a subform - tested)
      '179 SubdatasheetName [Auto]
      '180 LinkChildFields
      '181 LinkMasterFields
      'using the LinkChildFields property as an error test for a form works OK

      ' create an error to exclude datasheet forms
      If Err = 0 Then Debug.Print Screen.ActiveDatasheet.Properties("LinkChildFields").Value

      ' set frm to active datasheet table/query if no error triggered above
      If Err = 0 Then Set frm = Screen.ActiveDatasheet Else Exit Function

      'We just need to run this code once on each active datasheet to lock the data
      If cfrm Is Nothing Or Not frm Is cfrm Then

            Set cfrm = frm
            'Debug.Print frm.Name

            'make DS table/query read only (but not DS form)
            frm.AllowEdits = False
            frm.AllowAdditions = False
            frm.AllowDeletions = False
            frm.AllowFilters = False      'Optional

      End If

End Function

The code checks for any datasheet (table or query) which is currently open and active.
It then sets the properties AllowEdits / AllowAdditions / AllowDeletions to be false.

If preferred, you can also set AllowFilters to be false

NOTE:
Datasheet forms have been excluded as their properties can be directly set in the form design.
All other form types (single / continuous / split) are also not affected by the code.

The code is called from a hidden form (frmHide) which is loaded when the app starts.
A timer event is used with a short interval to check for the currently active datasheet and alter its properties.

Private Sub Form_Timer()
      Call SetTableQueryReadOnly
End Sub

I have used an interval of 500 milliseconds (0.5 s) but this can be altered as required.

NOTE:
1.   As written, the code will apply to the data in ALL tables and queries making them all READ ONLY.
      However, if preferred, individual tables / queries can be excluded from the code or it can be applied to specific tables / queries only.

2.   The status of tables and queries opened using VBA code depends on the datamode argument (acAdd / acEdit / acReadOnly) where the default is acEdit
      For example: DoCmd.OpenTable "Students", , acEdit or just DoCmd.OpenTable "Students" WILL both be editable



Video

UPDATE 27 Apr 2024
I have just released a YouTube video (8:30) with a detailed explanation of how this app works.
You can watch the Protect Tables and Queries video on my Isladogs YouTube channel or you can click below:



If you liked the video, please subscribe to my Isladogs on Access channel on YouTube. Thanks.



Summary

This approach is intended to deter most users from unwanted casual modification of data. It is NOT designed to block knowledgeable and determined hackers.

Whilst the data in tables and queries has been made READ ONLY, users are still able to change the design of tables and queries and export them as e.g. Excel or text files.


This functionality is still available even in ACCDE files.

This shortcoming will be addressed in the second part of this article Lock Down Database Objects

If you feel it is necessary to apply other more stringent security measures, see my 3-part article Improve Security in Access Databases



Feedback

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 27 Apr 2024

Return to Example Databases Page

Return to Top