Solved Locking Certain Records in Access Table (1 Viewer)

Pac-Man

Active member
Local time
Today, 05:45
Joined
Apr 14, 2020
Messages
408
Hello,

Is there a way to record certain records in a table so that they can't be edited by any form? Let me explain a bit in detail. Say I've a table tblReports and a form frmReports. This form is used to edit and add new records in tblReports. For certain records, I want that these can't be edited. So is there a way?

Best Regards
Abdullah
 

isladogs

MVP / VIP
Local time
Today, 00:45
Joined
Jan 14, 2017
Messages
18,186
Consider adding a boolean field Locked with default value false.
Then add a Locked checkbox on the form. It would need to be a single form - one record displayed at a time
If checked, then set all controls locked for that record - otherwise unlocked.

Obviously this wouldn't work for users directly editing records via a table or query but you should never allow that anyway.
You would also need to consider how that locked setting could be overridden if necessary
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:45
Joined
Oct 29, 2018
Messages
21,358
Hi. I think there are several ways, but not sure how effective each of them are. For example, you can have code in the Form's Current event to lock or unlock the current record, but that won't stop anyone from changing the data in the table. You can also try to use a data macro to avoid manual changes to the table.
 

Pac-Man

Active member
Local time
Today, 05:45
Joined
Apr 14, 2020
Messages
408
Consider adding a boolean field Locked with default value false.
Then add a Locked checkbox on the form. It would need to be a single form - one record displayed at a time
If checked, then set all controls locked for that record - otherwise unlocked.

Obviously this wouldn't work for users directly editing records via a table or query but you should never allow that anyway.
You would also need to consider how that locked setting could be overridden if necessary
Hi. I think there are several ways, but not sure how effective each of them are. For example, you can have code in the Form's Current event to lock or unlock the current record, but that won't stop anyone from changing the data in the table. You can also try to use a data macro to avoid manual changes to the table.
Thanks for reply. Directly editing in the table is not allowed to the basic users. I intend to lock records in the single form.

Actually few records in the table contain report no which is in older format. I just want to lock then so that user can't update them to newer format through the form. Also I intend to lock the record that are one month older.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:45
Joined
Feb 28, 2001
Messages
27,001
Let's be clear. There is actually no way to prevent a user from editing anything. That is, there is no mechanism in Access to lock a record on a permanent or semi-permanent basis. But YOU can make your forms refuse to do the changes by adding data to that form to act as a locking indicator. Then, if your forms cooperate with each other (and with you) then you can protect records that are appropriately marked by your chosen locking indication method. If your users cannot see the tables or queries directly to get to the fields therein, that would give you some protection and some chance at getting the desired locking result. But it is definitely a case of having to "roll your own" security.
 

Pac-Man

Active member
Local time
Today, 05:45
Joined
Apr 14, 2020
Messages
408
Let's be clear. There is actually no way to prevent a user from editing anything. That is, there is no mechanism in Access to lock a record on a permanent or semi-permanent basis. But YOU can make your forms refuse to do the changes by adding data to that form to act as a locking indicator. Then, if your forms cooperate with each other (and with you) then you can protect records that are appropriately marked by your chosen locking indication method. If your users cannot see the tables or queries directly to get to the fields therein, that would give you some protection and some chance at getting the desired locking result. But it is definitely a case of having to "roll your own" security.
Thanks for reply. Intention of locking is not too prevent dedicated hacker/adversary but is to prevent accidental editing of records. And if I could manage to implement (if I could make my forms to cooperate with each other and me) the method laid out by @isladogs, I hope I could achieve the desired locking. Users of my database are not so much advanced users of Access so I believe that might work.
 

isladogs

MVP / VIP
Local time
Today, 00:45
Joined
Jan 14, 2017
Messages
18,186
@The_Doc_Man
Actually it is possible to make a table read only as the attached example indicates.
However, the method used is not widely known and doing so is not appropriate for the OP in this instance.

NOTE The zip file contains a 32-bit ACCDE. I can supply a 64-bit version on request.
 

Attachments

  • DeepHideTablesExample.zip
    28.5 KB · Views: 195

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:45
Joined
May 7, 2009
Messages
19,169
deep hiding can damage your Attachment field unusable.
 

isladogs

MVP / VIP
Local time
Today, 00:45
Joined
Jan 14, 2017
Messages
18,186
I never use attachment fields.
However, that's the first I've heard of any issues related to deep hiding a table containing them and can't see why there would be a problem doing so.
Have you a link where I can find out more?
 
Last edited:

Pac-Man

Active member
Local time
Today, 05:45
Joined
Apr 14, 2020
Messages
408
I have had the attachment problem when I deep hidden the table that contained the attachment. I discussed the issue in this post.
 

isladogs

MVP / VIP
Local time
Today, 00:45
Joined
Jan 14, 2017
Messages
18,186
I have had the attachment problem when I deep hidden the table that contained the attachment. I discussed the issue in this post.
I've just tested this and found the same issue but I also found out how to get the attached files back (or prevent them being deleted) when deep hiding the table. I will explain in the other thread linked above
 

Pac-Man

Active member
Local time
Today, 05:45
Joined
Apr 14, 2020
Messages
408
I've successfully implemented locking records by following method suggested by @isladogs in post #2. However I also want to lock records automatically after they are one month old. How can I do it and where (which event) would the code be inserted to lock one month old records efficiently (without slowing the database).

I used the word efficiently because I take it as I have to check each and every record every time the form is loaded (if I have to insert the code in form load event) by using if statement and calculating difference of Now() and ReportDate and then setting the IsLocked field to Yes.

If no of records is increased then it might get slow because the program has to checked for all the records.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:45
Joined
May 7, 2009
Messages
19,169
use the Form's Current event:

Code:
private sub Form_Current()
    me.AllowEdits = True
    If Not Me.NewRecord Then
        if datediff("m", yourDateField, Date) > 0 then
            me.AllowEdits = False
        end if
    end if
End sub
 

Pac-Man

Active member
Local time
Today, 05:45
Joined
Apr 14, 2020
Messages
408
use the Form's Current event:

Code:
private sub Form_Current()
    me.AllowEdits = True
    If Not Me.NewRecord Then
        if datediff("m", yourDateField, Date) > 0 then
            me.AllowEdits = False
        end if
    end if
End sub
Thanks a lot for the code. I'll check it and will post here.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:45
Joined
Feb 28, 2001
Messages
27,001
If you want something relatively fast and efficient that is date related for that locking, consider something like this:

Code:
UPDATE mytable SET lockflag = TRUE WHERE NZ(recorddate, Date) < DateDiff( "d", -30, Date ) AND lockflag = FALSE  ;

In English, set the flag on any record that is still not set after 30 days. And you could pick -31 or -365 or whatever number of days is right. You could also pick another interval such as "m" for months, but if so, don't pick interval -1 because the granularity might lead to premature flag flips. I would strongly recommend using days ("d") and leave it as a number of days before doing the reset.

Now, WHEN to run it is as important as what to run.

You don't want to automatically run code for every user every time they touch every little thing. This is something that should be run at most once per user per session and even that might be a bit much.

If you have a switchboard form or dispatcher form that everybody runs when the first load the DB AND it stays open for the duration of the run, put it in the OnLoad of that form. The IDEAL situation is that if you (a) have identifiable login names and (b) you log in daily, make this only run when YOU log in even though the code would be in the startup form for everyone.

Note that I included a test for the lockflag. That is because without it, you would update every record and that would lead to database bloat. It would require you to do frequent Compact & Repair to the back end file. That is also why I suggested that only you run it, even if you make the run painless.
 

isladogs

MVP / VIP
Local time
Today, 00:45
Joined
Jan 14, 2017
Messages
18,186
@The_Doc_Man
I agree with most of your response and would also have suggested using a simple update query for this purpose
It also makes sense to only update the boolean field to True where it is currently false.

However...
Note that I included a test for the lockflag. That is because without it, you would update every record and that would lead to database bloat. It would require you to do frequent Compact & Repair to the back end file.

Updating a boolean field to true or false will not change the number of bytes used (1 byte per record) so will not increase the file size.
It will cause ZERO database bloat no matter how many times it is done nor how many records there are.
Therefore it will not lead to the need for compact & repair!

Your point would of course be valid if you were referring to a text field.
Increasing the length of the text will increase the the number of bytes needed & so increase file size (bloat)

For more info on Access datatypes & file size, see https://support.microsoft.com/en-us...atabases-df2b83ba-cef6-436d-b679-3418f622e482
NOTE: the link also contains info about the new Date/Time Extrended datatype
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:45
Joined
Feb 28, 2001
Messages
27,001
Colin: Read the section on UPDATE in the language specification for SQL (either 1988 OR 1992) and you would find the requirement that both the new and the old versions of records must be available for cases where someone has a thread open for reading while someone else is doing an update. The SQL specification pretty much states that even if you aren't changing that particular field, you STILL have to make a separate updated copy of the record in order to keep it from instantly causing destructive interference. It's a data integrity sort of thing. The SQL specification IS available online.

If that rule is being followed, then there are two copies of the record existing simultaneously, each representing a snapshot of the data set taken at different times. If you are going to have the OpenRecordset( x, dbSeeUpdates ) option (or whatever it is called), that HAS to happen that way. Which means that when the thread doing reading releases the record, then and only then can the "real" record be updated and the temporary image be released. That temporary co-existence of records means that you WILL get bloat for native Access backends.

Part of that is because the Access file does not pre-allocate bucket lists like, say, ORACLE. It is possible for you to tell a running ORACLE system to reclaim space and it will do so. Access cannot do that. I won't make a guess for SQL Server. But regardless of how that intermediate value is retained, there is a requirement to retain it. Bloating is a side effect of dynamic creation and deletion of records including update instances. Repeated updates will have that effect.
 

isladogs

MVP / VIP
Local time
Today, 00:45
Joined
Jan 14, 2017
Messages
18,186
I'm fully aware of the principles you are describing but disagree with your conclusions.
Remember an update query does not create a result set & it cannot be reversed.
Jumping back & forth between Access, Oracle & SQL whilst discussing this isn't necessarily helpful in my opinion.

I've tested this with a large recordset of 1 million records in both a standalone Access database and in a split FE/BE setup with more than one copy of the FE open and the table being updated open for editing in FE #1 whilst it is simultaneously being updated in FE #2.
Result: ZERO increase in file size as a result of repeatedly updating the boolean field from false to true to false again.

As already stated, doing updates on a text field will increase file size as it will modify the number of bytes used by each record depending on the amount of text entered.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:45
Joined
Feb 28, 2001
Messages
27,001
DB.Execute "UPDATE .....", dbFailOnError will roll back an incomplete update. A BEGIN/COMMIT sequence that involves an UPDATE can be rolled back even if the UPDATE wasn't the last action in sequence. You CANNOT do either of those things if you perform that update "in place" even if it is only a Yes/No field.
 

Pac-Man

Active member
Local time
Today, 05:45
Joined
Apr 14, 2020
Messages
408
If you have a switchboard form or dispatcher form that everybody runs when the first load the DB AND it stays open for the duration of the run, put it in the OnLoad of that form. The IDEAL situation is that if you (a) have identifiable login names and (b) you log in daily, make this only run when YOU log in even though the code would be in the startup form for everyone.
Thanks a lot for reply. And yes I do have a switchboard and login system in place. And also I login daily so it can be set for me to run this query automatically for me. Thanks again for suggestion and SQL code.
 

Users who are viewing this thread

Top Bottom