Prevent multiple access to table

[allochthonous]

Is there any way that I prevent more than one user of a FE/BE database accessing the same table at the same time?

I tried using

If SysCmd(acSysCmdGetObjectState, acTable, "tblBrandsEditTEMP") <> 0 Then
    MsgBox "To maintain data integrity, only one user may edit the Brands table at a time.  Please try again."
Else
    DoCmd.OpenForm "frmEditBrands", acNormal
    DoCmd.Close acForm, "frmMaintMainMenu"
End If

But this only appears to work within the same database instance. If I attempt to open a form that accesses this table in another copy of the FE, it opens as normal.



I need a way to check to see if anyone else is accessing the table, and if so, prevent it. I create the table when the form is opened, and clear it when the form is closed, so worst case, i could check for contents in the table, but this seems sloppy.

Thanks

Paul

[June7]

This is a 'temp table' that is populated only for the duration of a procedure and each user needs to be able to populate the table independent of other users?

Put temp tables in the frontend.

[allochthonous]

It is for maintenance of reference tables. For security's sake, I make a copy of the table before any additions/deletions/edits are made, and then once the user decides he is done with the changes, he can click a button to "commit" the changes, which backs up the original table, and replaces it with this copy. If the user decides to cancel the changes, he can click cancel, which leaves the primary table untouched.

I don't want more than one person doing maintenance at one time. Sure, when it comes to the actual functionality of the database (tracking transactions) it will be a multi user environment.

PK

[June7]

What do you mean by 'maintenance of reference tables'? What are these tables for? If this is just adding/deleting (why delete?)/editing records, why can't more than one user at a time work with the table? Everyone is allowed to do 'maintenance'?

I don't know any way to lock a table or to even detect that a user is working with a particular table or form.

[allochthonous]

The goal of the database was to be as easy to use as possible. I did not want to have to worry about levels of permissions etc. So yes, the thought was that anyone would be allowed to do maintenance. This table is used to store the text values of BrandNames and their corresponding ID values. There will also be a similar table for ProductTypes and Customers. The users will need a way to add new Brands, Types, and Customers, as well as edit spellings. The user is warned as they enter "Edit Mode" about the importance of not changing the MEANING of a value. I was hesitant to add the ability to delete, and therefore only allow the delete if there are no records with the ID value already on the transaction table.

Likewise, I did not want the user to be able to edit these tables directly (the Customer table may be the exception) until they were "double sure" they wanted to commit the changes. Hence the creation of the "temp" copy of the table.

Does this make sense?

PK

[June7]

To me, this 'maintenance' is just data management, no different from working with any other data in the database. I don't see the need for the table copy, never done anything like that. Data is committed to table when move to another record, close table/form, or run 'save' command. Let users edit the data and offer them an 'abort/cancel' option to undo entries if they change their mind. Other users who try to work with the same record at same time (probably highly unlikely) will get an Access warning message.

[allochthonous]

This was originally how i was going to do it, but then i thought i needed some kind of "abort/cancel" functionality as you mention to undo everthing they have done. How can i accomplish this on the "live" table once the record has been written to the table?

PK

[June7]

Allow data entry only through forms. Set form Cycle property to Current Record. 'Cancel' button on form. Click event code to undo. Review http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

[allochthonous]

And this will undo the changes made during the entire session while the form was open, regardless of how many records were changed? The table is displayed through a subform on a form. Will this still work?

PK

[allochthonous]

In case it is unclear, I have attached the database to demonstrate what kind of functionality we are talking about. I wanted to be able to view and edit all of the Brands at once, rather than one record at a time.

[June7]

No, the Undo method would act on only the current record. Sorry, I have no solution for your situation.

Can't really analyze your db without the backend.

[allochthonous]

Do you think what I am trying to do is poor design?

[June7]

Is the need for mass editing of brands a frequent event? How many at a time are involved?

I don't know that this is bad but certainly complicates. Regardless, only one record at a time can be edited unless you want to run an UPDATE sql action. Require user to confirm record before moving to next.