Question on record locking management

donwiss

Board Regular
Joined
Jul 5, 2020
Messages
62
Platform
  1. Windows
I'm developing an application that is all Excel VBA Forms with the data in Access. I have no database background. I developed a record locking system not knowing just what real ones have.

This is my locking management form. There are three types of file systems, hence some blanks. The one with a blank in the Record Name column does have record nnames, but they are edited simultaneously, and so all locked together.

records-lock-onoff.gif


When I added the Last Saved column, what I really wanted to add was the timestamp of when it was locked. Of course, I could add that field to each table, and only complicate things a little.

So my question is just what does a real locking system have? If I add the lock timestamp, does anyone care about when it was last saved?
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

JonXL

Active Member
Joined
Feb 5, 2018
Messages
449
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Record locking should be pretty straight-forward. For one, Access has built-in record locking of a decent quality. But if you want to go beyond that (and you may need to), the approach I use is:
  1. Build a table for record locks with these fields:
    • ID
    • RecordID
    • TableName
    • User
    • LockTime
  2. When a user opens a record check the record lock table to see if it's locked (and do whatever you need based on that); you will be checking the table for a record matching the ID and table name of the record the user is opening. You can use the info in the lock table to tell the user who has it locked - if that is useful for you.
  3. Also when a user opens a record (after passing step 2, if you have it set to disallow even opening the record, etc. when it's already in use), use code to add the information to the record lock table of what record they open. You need to add the RecordID (ID from the record's table), the table name the record is on, something to identify the user - which you seem to have figured out - and the time they accessed the record (the last isn't completely necessary since you won't be using this table for auditing, but why not?).
  4. When a user closes a record, delete the corresponding entry in the record lock table so that when other users run their step 2 check, it doesn't show up as locked.
  5. Make sure to build separate functionality to allow users to manually remove locks they have set but may not have cleared correctly (eg, app crashed). And also make sure someone has the ability to clear any and all locks whether set by them or not (eg, an admin). All this is to make sure you can't end up with users permanently locking records they aren't able to unlock.
To accomplish 2 and 3, I use a custom function (but I do this in Access, not Excel) so when I run the code to open the form, instead of opening it with typical code, I call my function (I think I call it LockAndLoad() but I'd have to dig to find for sure what I call it or the specifics of its code). The function takes the parameters of what I want to open and then handles checking for it being locked and either opening the record and locking it if it isn't already locked or throwing an error to the user if it is. This way I don't write record locking into every call I make to load a record, I just use the same code as everyone else except instead of the built-in DoCmd.OpenForm method (Access), I use my custom LockAndLoad() function.

If you want specifics, let me know. But I want to make clear again that I do all of this in Access and not Excel, so my specific code examples - if I can find them - may not be exactly what you need. The methodology should be the same, though.
 
Solution

donwiss

Board Regular
Joined
Jul 5, 2020
Messages
62
Platform
  1. Windows
Thanks for the detailed explanation of how a database pro would do this. My system is completely written and functional. What I did was to add a UserName field to each lockable table. Either there is a name in it, or it is blank.

The form above is available to all users. The application will never have many users. They can unlock their own crashes, though I slipped in a feature where a file locked by the user, and the user is trying to open it, it opens. Then like normal, unlocked when closed.

I see you have LockTime. Good name. I will add this field to all my lockable tables. And in my display form I'll simply replace the Last Saved column with the Lock Time.
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
449
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks for the detailed explanation of how a database pro would do this. My system is completely written and functional. What I did was to add a UserName field to each lockable table. Either there is a name in it, or it is blank.

The form above is available to all users. The application will never have many users. They can unlock their own crashes, though I slipped in a feature where a file locked by the user, and the user is trying to open it, it opens. Then like normal, unlocked when closed.

I see you have LockTime. Good name. I will add this field to all my lockable tables. And in my display form I'll simply replace the Last Saved column with the Lock Time.
I think there's a misunderstanding.

You should not add fields to your existing tables but rather create a separate table that you write to and delete from as records are locked and unlocked.
 

donwiss

Board Regular
Joined
Jul 5, 2020
Messages
62
Platform
  1. Windows

ADVERTISEMENT

What is wrong with my way? I added the LockTime field. It works. Ties to a record or table are never held open. As sole developer I'm in control of everything.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,825
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

It would almost certainly be better to use the locking provided by the database application you are using rather than using your own. In fact, if you are using Access you are probably using its locking in one way or another already.
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
449
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
What is wrong with my way? I added the LockTime field. It works. Ties to a record or table are never held open. As sole developer I'm in control of everything.
I have no doubt it can work.

But it's not as normalized as it could be since record locks are technically different information from the other information your tables are holding.

This complicates things. For example, the function I use works with every table immediately and doesn't need to know anything about the table it's 'locking' because it does all its work on its own table.

But if your method's working it's working. And I'm genuinely glad to hear that it is.
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
449
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
It would almost certainly be better to use the locking provided by the database application you are using rather than using your own. In fact, if you are using Access you are probably using its locking in one way or another already.

Yep. I only use my custom locking when I have to work with unbound forms.
 

donwiss

Board Regular
Joined
Jul 5, 2020
Messages
62
Platform
  1. Windows
But it's not as normalized as it could be since record locks are technically different information from the other information your tables are holding.

I've been thinking about just what is negative about my way. All I could come up with is my way is slow in listing the locked files. I have to issue a SQL query for each lockable table. I decided I would check any table that was lockable, whether locking was turned on or not.

But I had completely forgotten about normalization!! Of course, my way fails on that. I haven't heard that term in decades, and the simplicity of all in a single record was the way mine got designed. In my case, the tables will never be so big that the space taken matters. But hitting the 255 columns limit could well matter in the future.

My code does need to know the three styles of tables I have, so not very portable. But this is a one time project for me.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,832
Messages
5,627,150
Members
416,223
Latest member
RichardHell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top