Deletion in a Multiuser environment.

[rookery]

I have copies of a Front-End database installed on 2 computers. Obviously they are both linked to the same back-end database. If I delete a record from a Form say on PC1, if I then switch to the same form on PC2, I get the dreaded: "#Deleted" message, in its place.

I have tried using the Refresh and the Requery method without much success. Does anyone know of a solution or workaround?

 

[TrojanRabbit]

Interesting that Refresh and Requery don't work. Maybe have the form call OpenForm with itself. Never tried this, so I don't know if you can.

 

[danvlas]

I agree on Refresh: it just recalculates the recordset clone available at that moment.
But Requery...I may be crazy, but it's impossible that you use Requery and still get the #Deleted message.

Are you talking about a subform that still shows you #Deleted after requerying the main form? Then, from the main form, try:

Me("SubFormName&quot.Requery

And I can't believe you go to the second PC, open the form and you get the error... Is the form by any chance already open when you go to the second PC?

More details will help a lot...

Dan

 

[rookery]

Thanks guys for your thoughts. Dan, apologies for the sketchy info - let me be a bit more thorough:

Same Form on 2 PC's. On each Form is a ListBox and a Delete button. When I delete a record from the ListBox on PC1 I use the Requery method and the "#Deleted" message disappears. However, I go to PC2 (where the same Form is already open) and "#Deleted" is still there.

So No I dont open the Form on PC2 - you are correct in your assumption that the Form is already open. Know of any way to get rid of this message other than perhaps setting an OnTimer event which I'm loathe to do?

 

[danvlas]

Actually, here's what happens on second PC:

Open the form->populate the listbox->[delete something from another computer]->Access automatic Refresh -> #Deleted.
Since no automatic Requery takes place, you still see the error.
And you cannot use Requery on one PC to have effect on another one.

You can use the Timer, but what Interval do you plan to choose? Too long-> you don't get rid of the problem. Too-short-> use more resources...
I'd try to Requery the list box in its Click event...This way, just click on #Deleted and it disappears...

Regards,
Dan

 

[rookery]

These were my sentiments entirely regarding the OnTimer event. However your idea re: Requery on the ListBox Click event is an interesting one.

However, surely this is a common problem encountered when developing multiuser apps and I'm surprised that there isnt a more well-documented solution.

 

[Paddyo]

Some thoughts re writing Multi-user apps


If you are going to write Multi-user applications you need to be in complete control of the data. MsAccess is great on one level, the way you can bind controls to datasources etc., but when you need to control what appears on forms, saving the data when you want to save it and so on I would suggest that you do not bind any controls, well maybe some, and that you use VBA to retrieve and save data.

With your specific example, the user on PC2 sees the #delete record. Would it not be better to have an unbound listbox which would not report the deleted record and if the user clicked on it tell him that it has been deleted by another user. You could put a refresh button on the form.

Good Multiuser applications need to be programmed.

My 2 cents for what they are worth.

 

[danvlas]

Paddyo: Totally agree with you about having full control over the data.

I have one question to your post regarding this specific situation:

"unbound listbox which would not report the deleted record"

Actually the user will rely on that record, as it would show as a valid one. What happens if let's say user double clicks the deleted-but still showing-item and expects the program to open another form, or a report or whatever other information on the basis that record?
Guess who'll be blamed for not getting the results he expects...

My personal opinion is that a deleted record should either show as deleted, or disappear for good, either one as fast as possible.


Regards,

Dan

 

[Paddyo]

Hi Dan

"and if the user clicked on it tell him that it has been deleted by another user" - from my previous post.

I think that in a multi-user situation users expect data to be changed, deleted etc by other users. As I said above, multi-user apps need to be programmed for eventualities such as you have pointed out.

You are correct that the best situation would be to show that record has been deleted - while still showing the original info about the record. As said in previous posts the timer is your only man.

I think that the automatic removal of the record form the list box could look quite peculiar and could cause some problems. Maybe not a problem with this specific problem but consider this - If you have double clicked on the list box and the program does 'its tbing' - what if 'its thing' refers back to the selected item in the list box, which if we had automatically deleted records would have changed.

I suppose you could turn the timer off once the list box had been clicked - I having a conversation with myself now.

Anyway
I think an entire forum could be dedicated to how to write multi-user apps correctly. I have been doing it for 17 years now and it still amazes me how little I know.

Paul

 

[danvlas]

I know what you mean...
No matter how much you work on data security, the non-technical user always finds a way to circumvent it

Dan

 

[rookery]

Paddy in answer to your point re: unbound list boxes and, as far as I can make out, to reiterate Dans point, both of my Listboxes are already Unbound. They both also display the dreaded "#Deleted" message.

My solution is just to Requery the box if a user clicks on the message.

As an aside, whilst I appreciate your comments, the fact that you're still having problems after 17 years fills me with terror!! Does it not get any easier!?

 

[rookery]

As a follow-p am I right in saying that there must be a way of checking if any other users are looking at the same record as yourself?

For example, this would be particularly useful if I was about to delete a record and up popped a warning saying that another user was viewing the same info. Otherwise, as I'm sure you're well aware, the other user just gets a screen of "#Deleted"'s.

Any ideas?

 

[Paddyo]

Hi Rookery

Maybe I was being a bit pessimistic about the 17 years - it does get easier - but new situations arise all of the time.

I cant understand why its still coming up with the #deleted if it is unbound. You must be binding it at run time.

To answer your question about knowing if another user is using a particular record - I dont think there is any easy way of doing this. A method I have used is to have a locking table - when a user selects a record, the user number and some unique identifier is recorded in the locking table. when another user attempts to use that record, a check is made in the locking table to see if another has already got it open. If so the second user cannot use it. When the first user is finished with the record he deletes the record from the lock table.

This needs to be programmed carefully, to avoid locks etc. You must trap error codes and retry if the lock error codes are returned.

You must also be aware that if a user crashes, the record will still be in the lock table and so you nust have some facility to remove these residual records.


another method used by some poeple is to have a field in the record which indicates if the record is in use or not. A simple boolean field might do.

Hope these comments help.

Paul

 

[rookery]

Paul

Thanks for your comments. I can assure you that the ListBox isnt bound in any shape or form - however it is on a bound Form. I dont know if this might have any impact or effect?

Also, I like the idea of a Locking Table (why didnt I think of that) and will let you know how I get on.

 

[danvlas]

The ListBox is not bound. However, its rowsource is a simple Select statement that returns a recordset that is refreshed by Access at certain intervals. That's why the #Deleted appears.

If you use a Groups/Totals query to return the values for the list box, then you'll have a snapshot-type recordset which will retain the original values until requeried.
Just modify the list box row source by inserting a Group By Field1, Field2 etc and that's it.

You won't see the #Deleted message again. But you will have the deleted data displayed on screen.

A locking table...will need locking information about itself...maybe another locking table...which will need...you know...

You can check if a record is edited (and therefore) locked by opening a one-row recordset locked as dbPessimistic and try an Edit...It will generate an error. Trap the error and there you are.

But make sure you set the general options for your database objects to lock the edited record.

Dan

 

[Paddyo]

Hi Danvlas

The locking table idea works well, without the having a locking table to check the lock table and so on....

Lets say we have an account which we want to stop other users amending.

In the lock table we have the following text fields
Lockitem - (Unique index)
LockUser - shows which user has written the record

The account number is "12345"
To this we add "AccAmend" giving the unique field/key of "AccAmend12345"

First thing we do is search the lock table using this key to see if there a record there with this unique key. If there is then someone else is amending this account. Otherwise insert the record.

Alternatively you could try inserting the record which will return a duplicate index error if it already exists indicating that someone else is amending the account. This method will add the record if no duplicate record is found.

Whe you are finished amending the record you must delete the record with the key "AccAmend12345" to allow other users amend the account.

Using this lock table you must have proper error trapping in place - especially for MsAccess locking.

I have implemented this method in a VB environment and it works very well. I just call the lock or unlock functions passing the appropriate parameters. Because this method is not actually locking records you can use it to stop other users doing things while for example you maybe running a period end routine or some maintenance routine.

I know I have gone on a bit but believe me it does work.

Paul