Question on record locking management

[donwiss]

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.

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?

 

[JonXL]

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.

 

[donwiss]

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]

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]

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.

 

[donwiss]

I have a mockup of the application I'm developing. My file system is fully discussed on this page:

BondCalc: File Discussion

 

[xenou]

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]

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]

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]

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.