Need helt developing either a que-system or an effective anti-concurrency function.

[Inkognito]

Hi there!

I have been searching different forums, but I can't seem to find any answer to this excact issue, and this is without doubt the best access forum, so here goes.

I have a little project with a few different challenges.
This might be a little long to read, but I would like to just write my thought-progress so far, and if anyone has any better solution to the issues, i would be grateful to hear them

I have been getting a lot of help here with my last project, which was a database for a boat-harbour. It was used to schedule boats to be put out of - or into - the water.
They have gotten lazy now though, and they wish to no longer schedule this themselves, but rather have the customers directly schedule for them selves.

This means I am re-hired to get this database online, and like always, I don't have all the answers for how to solve it.
The DB has a lot of vba-code in it, so porting to SQL or using "pages" would mean losing crucial functionability.
Using an .adp file linked to an SQL-server or splitting the DB to frontend and backend and distributing the .adp/frontend would require all the users to have access installed, which can not be a requirement for hundreds of boat-people.

The only good solution I have come up with is to distribute an RDP-link, and letting all the users connect to a stripped-down server with remote desktop, where the only application that is allowed to run is MS Access 03.
The RDP-part i can pull of without difficulties, but it would need a bit of programming on the access bit to make it ready for more users.

The main problem would be concurrency. I need to prevent two users from modifying the same record at the same time.
Each record represents one day, and has 28 different time-choices for their schedule. Therefore it is very likely that two people would be looking at the same record at the same time.
I have tried the integrated record-locking function in access advanced options, but it is not working. It would not be a perfect solution anyways.
The perfect solution would be some kind of que system that would let only one person book their schedule at once, and when they are done, the next user can log in.
Or any solution that effectivly prevents users from editing the same record at the same time.

I was thinking that it would be possible to maybe make a startup-form with VBA that only allowed one person to access the database at once, making the others wait for their turn.

Any thoughts, ideas, solution, suggestions, reflections or anything on this from anyone?

 

[JamesMcS]

Yeah, I think there's a general setting somewhere that allows only one user to access one record at a time, hang on I'll have a look now.

 

[JamesMcS]

It's under advanced options, have a look for record locking in the help file.

Online-wise, Mr. B recommended a free MS tool you can use to create a page and link to your DB:

http://www.microsoft.com/express/Web/

 

[Inkognito]

Yeah, I have found that one too, it's not really having any effect though :/

I have tried the integrated record-locking function in access advanced options, but it is not working.

I believe I read something about it only taking effect when it is opened from a network share etc..

 

[JamesMcS]

Hmmm.... well if it's going over the web eventually that would probably count as a network share (wild guess)

Not really a solution to the locking problem, but how about treating the bookings as requests, subject to confirmation? You could show the bookings form, but get it to append the booking to another table as a request, then if there's a clash the customer can ring the client? (Straw clutching)

 

[Inkognito]

Since I was planning on letting the users dial in with RDP, it wouldn't really be a network share, everything would happend locally on the server..

The idea of making the bookings go into a separate form, and then letting another function assign the bookings to the actual schedule is a good idea, but it would involve a lot of programming and redesigning of the huge database. It would also require the system to somehow report back to all the users, telling them whether their booking was accepted.

I have two weeks to finish this, and as I am a bit unsteady on my access and vba, I don't think i dear to start to reprogram the whole booking form like that. It would just be to massive to program as I also has a day job very nice idea though

 

[JamesMcS]

Damn those day jobs... RDP? Is that remote desktop? Risky letting the public get into the PC that way.

Have you had time to look at that link I provided? I haven't used the programs yet but they should allow you to knock a form together fairly quickly...

 

[Inkognito]

Yeah, I know it could be quite risky, but I got some experience with totally stripping down a computer for public use. Removing access to explorer, taskmgt, helpmgr etc, so I guess it would be quite safe.

I read about the programs on your link, they seem great, but I have some very necessary visual basic programming in my forms, and since the programs on the link is based on SQL, I don't know how to preserve the VBA-functionability.
My forms got like a LOT of buttons and automatic functions, checking for example that two sail boats are not booked the same hour, as they take longer
I don't know if SQL have any function to implement all that VBA-code.

If I had only programmed the whole thing in SQL from the start, this would have been easier, but I used a year on the VBA-code for the database alone, so I don't think I want to be doing it over again

 

[JamesMcS]

Sorry bud I'm stumped on that one, annoying that the record locking option doesn't work as it says on the tin!

 

[JamesMcS]

One last stab.... there's a record lock property in the form's design. Does that one work??

 

[Inkognito]

Yeah, I know, really annoying, only effect it has is that the users get this message: "This record has been edited by another uses since you started, wish to save record or scrap changes?". I guess that would be a solution in many cases, but in my case the users should not be allowed to overwrite the other users schedule.
Users are only allowed to add appointments and not edit others' in my form.

The record lock property, if I am looking the same place as you, seems to be the same as the tickbox in advanced options.
But there is an "allow edit" option too, maybe I can flag that setting everytime a user is entering a date-record. Might work, I'll play around with it a bit

Thanks for the help and effort so far!

Edit: This would of course lock out the whole form, and not just the current record need to adress the record and not the form somehow..

I think that locking records is not the way to go, I need to find a way to make sure only 1 person is using the database at a time.
There is no problem for the users to wait for their turn, so I have to make a que-system either within access or before they get to access.

 

[DCrake]

What about adding a Yes/No field to the booking table and as soon as a person hits a free record it flags that record with a True so if a person selects a record that is flagged as True it prevents them from editing it. Then when the users commits the change it resets the flag to False. Just an Idea.

 

[Inkognito]

That is pretty much what I am looking for yes!
I tried to think of ways to program something similiar, but that sound really simple. Thought of doing it with a textbox in the form, but that would flag every record with true
I think i might actually be able to program this, or else i will return

But just before i checked back to this forum, I got an idea too.
You know that message that says; "the record has been changed by another user since you started" with the three choices to save, drop or copy to clipboard?
If I somehow could replace that message with a code of my own, just stating: "This schedule has already been edited", and then reload the current record (so that the "on current" events is executed again).
Would that be possible?

 

[John.Woody]

The problem is that if 2 or more people are looking at the same data when one books it you need to make sure no one else can.

In one project the user queried the data to look for availability but when they went to book I opened a recordset with the options dbDenyWrite, which locked the table so no one else could write to it, requeried the data to confirm availability, wrote the record and then closed the recordset allowing other people to book. Key was keeping the recordset open for as short time as possible and deciding how to handle a second person trying to book at the same time, either putting them in a loop until the table was unlocked, or giving them a message asking them to try again in a few seconds. My enviroment was an office with a max of 8 users and giving them a button asking them to try again worked well. In reallity the table was locked for about 1 second for each booking and with only 8 users the chances of two users booking at the same time was not very likely.

hth

 

[gemma-the-husky]

the prinicple of record locking is this

anyone should be able to read anything at the same time
only one writer should be able to write a record at a time

so one point about access is that (I think) under normal circumstances there is no difference between a recordset opened for editing purposes, and that same recordset opened for readonly puroses (eg, to produce a report). No user is explicitly a "reader". All are potentially "writers"

Now - the problem with deterministic locking is that if a user process locks a record at an early stage - and then leaves it locked (user leaves his PC, or through a programming error) other users can never see that record. In some cases it can cause users processes to log-jam waiting for the locked record to become unlocked. In worse cases it can cause a deadly embrace where two processes each lock a record required by the other process, and the whole system locks

therefore locking records is not to be undertaken lightly. Thought must be given to a mechanism to maintain the lock for a minimum time, and to release the lock in certain cases, maybe based on a timer, or maybe based on multiple re-read attempts, followed by a fail message.

Now I am not sure exactly how record locking in Access is implemented to do all this automatically, or whether all/any of these possibilities need to be considered and managed programmatically

-----------
now by default access uses something called optimistic locking, which is not locking as such, but consists of re-reading a record immediately before a write to see if it changed from when you first read it.

So - if you are only a reader, then this never applies
- if you are a writer, but the record didn't change then there is no problem - the write just completes.

ONLY, if it changed between you extracting the record, and now writing the data back, do you get the "another user changed the record" message, and you have to recover from this. (generally by aborting YOUR process)

Now in many cases there is actually no other user at all. What happens is that YOUR app (often inadvertently) opens the same record in two different ways, and therefore YOU are the other user who changed the record.

-----------------

In my experience, optimistic locking is adequate for virtually all purposes. I have never had to use any other locking method in any database. The message sounds like you are actually using this form of record locking - and the problem may well be that you have inadvertently got multiple processes runing on the same record, as described.

Personally, I would check this carefully, before deciding I needed to use any explicit form of record locking

 

[Inkognito]

Thanks John but my 'booking' system is made in a very weird way, as I just put it together one step at a time without really knowing what I was doing, so I don't think that would be a problem with my db.

I can definately work with the Locked-field solution, now I just need two pieces of code:

How to unflag the locked-field when someone changes to another record.
Like the opposite of:

Private Sub Form_Current()
Me.Locked = True

To be absolutely sure, I don't even want two people to be looking at the same date-record, at least not while both having the possibility to edit it.

So I would also need to make code for either completely disabling / greying out an entire date record, or just make access skip the dates that are flagging, possibly with a messagebox stating that the skipped record was in use.

If there is an easy way to do this on the top of anyones head, I would appreciate a hint would save me some time, else I will just ramble away in the code until i get it right as usual

 

[gemma-the-husky]

Thanks John but my 'booking' system is made in a very weird way, as I just put it together one step at a time without really knowing what I was doing, so I don't think that would be a problem with my db.

I can definately work with the Locked-field solution, now I just need two pieces of code:

How to unflag the locked-field when someone changes to another record.
Like the opposite of:

Private Sub Form_Current()
Me.Locked = True

To be absolutely sure, I don't even want two people to be looking at the same date-record, at least not while both having the possibility to edit it.

So I would also need to make code for either completely disabling / greying out an entire date record, or just make access skip the dates that are flagging, possibly with a messagebox stating that the skipped record was in use.

If there is an easy way to do this on the top of anyones head, I would appreciate a hint would save me some time, else I will just ramble away in the code until i get it right as usual

Not sure if you posted this while I was posting the post before this (if you see what I mean) but I wouldnt try to do this. It's a lot of work, and unnecessary. THe problem is releasing a software lock, as you have seen, which is not as easy as it sounds. You may end up with a load of "false" record locks in your data, which you then need a way of releasing.

I think if you understand exactly what Access record locking does, then you will be able to use intrinsic access methods to achieve what you want.

 

[Inkognito]

I agree that locking records in random creative ways is risky, but I was planning on simply letting the users automaticly skip the records that are "locked". They aren't really locked, I will just program in a function that makes the db skip those records. That way nothing could really get jammed.
And of course I need a few functions to both automaticly and manually unlock records that never get unflagged.

The optimistic-locking gives the users a choice to overwrite the other users changes in a record, and that is not good in this case.
This is a "first come, first serve" system, where the first user to register a specific schedual gets that schedual, so I want to completely eliminate the possibility of two persons even looking at the same date at the same time.

Therefore I believe this solution is good.
I have been testing with the optimistic and pesimistic locking, but it doesn't work the way the people at the harbour wants it to.
If only the choice-popup from the locking could be manipulated to no longer give the choice of overwriting the other changes.
So if anyone has a specific other alternative, I would love to hear it
In the mean time I will try to make this very custom solution work

 

[Inkognito]

While programming, I do see what you mean though.
Setting the lock-flag is very easy, but unflagging it is worse.
There is no event that I can see that triggers when you change to another record.

 

[Inkognito]

Seems I have to take to my senses and try to use the integrated record-lock function after all... all I would need is a way to replace the standard message with a function of my own. I know it is possible with error-messages, any chance it could be done with the recordlock-message too?