Prevent multiple access to table Access 2010

[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

 

[Remou]

You say you create the table when the form is opened, which suggests that checking if the table exists should be enough, but you then say you clear it, which sounds like you keep it. I rather hope you are not deleting and creating a table, because it will lead to considerable bloat.

I wonder about an approach that requires that only one user at a time can access a whole table in the back-end of a relational database. I suspect that it may be possible to solve your problem with transactions. If not, you might like to consider writing a record to a lock table. This could contain the user/pc name and alow you to tell others that such and such a suer is working on this at the moment.

http://lessthandot.com

 

[allochthonous]

Right now I am keeping the table, and just deleting records on close, and appending records when the form is opened. I could not recall which method resulted in bloat (can't this be combated with compact and repair on close of the db?)

What is the code to check for existence of a table? If i stick with the append/delete method, would I be safe in checking for records on the temp table? If records exist, then that would mean that someone else is maintaining.

Here is some more detail into what i am looking at:

The goal of the database was to be as easy to use as possible. 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.


PK

 

[allochthonous]

Forgot to ask, could you explain a little more about the "lock table" idea?

PK

 

[Remou]

It is generally recommended that compact on close is turned off, because it can lead to corruption. If you have enforced referential integrity, it should not be possible to delete a "used" brand.

I am afraid it all sounds a little unsafe. Are people entering transactions while the maintenance is occurring? Is it not possible that someone is editing in a way that would affect this? I wonder is it worth thinking about a maintenance mode that locks the whole back-end database for the period of maintenance? One thing that makes relational databases useful, is that changes occur in real time and there is a much smaller window for data to get out of synch, whereas a temp table could be held for a considerable time.

You might like to read

http://www.tek-tips.com/faqs.cfm?fid=184,

because the ideas may give you some options for a different approach. I don't quite agree about the thoughts on autonumbers, they are from an earlier development of Access.

I am not sure there is much point in the lock table having seen your description - records in your temp table would provide the same functionality.

http://lessthandot.com

 

[TheAceMan1]

How are ya allochthonous . . .

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

[blue]Recordset Locking[/blue] is the only easy method that comes to mind ... particularly for controlling an entire table. Consider

[blue]Using Recordset Locking: You use exclusive mode to lock an entire database. You use recordset locking, on the other hand, to [purple]specify locking for an entire table[/purple] in a shared database. You can specify a [purple]read lock[/purple] to prevent other users from reading records in the table, a [purple]write lock[/purple] to prevent other users from editing records in the table, or both. [purple]ADO and OLE DB don't currently support this kind of locking[/purple], so you must use DAO code if you require recordset locking. Recordset locking applies only to DAO table- and dynaset-type Recordset objects; it can't be used with DAO snapshot-type or forward-only – type Recordset objects, because these are inherently read-only objects. To implement recordset locking, Microsoft Jet places shared table-read and shared table-write locks.[/blue]

Basically you'd perform all your editing/saving thru DAO Recordsets using proper OpenRecordset paameters. I'm not sure at the moment the source of the quote above (which I've used sucessfully in the past) but I think it was [blue]vbcity[/blue]. As soon as I find the source in my library I'll post it.

Example:

[blue]   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   
   Set db = CurrentDb
   SQL = "SELECT ClientID, FName, LName " & _
         "FROM tblClients;"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset, [purple][b]dbDenyWrite[/b][/purple]) [green]'other users can't write to table![/green]
   rst.FindFirst "[ClientID] = 1212"
   
   With rst
      .Edit
      !FName = "NowIsTheTimeForAll"
      .Update
   End With
   
   Set rst = Nothing
   Set db = Nothing[/blue]

... and I've found the source of the info: [URL unfurl="true"]http://vbcity.com/forums/t/15523.aspx[/url]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[allochthonous]

Remou: Yes, I check for the usage of the Brand code before I allow deletion from the table.

Yes, in theory, users could be entering transactions at the time the "maintenance" is going on, but since the CODE is written to the Transactions table, it should not matter what is going on editing wise. Deletions from the Brands and Types tables could be an issue, and I have considered just not allowing Deletes at all or safety's sake.

Locking the entire back end would not be a terrible idea, as these maintenance instances should be short. This database will only have 3 or 4 users at a time, all within a few feet from one another, if I understand. It is for a small gun shop to track transactions, and all of the users should be behind the same sales counter.

TheAceMan1: This is something to consider. There have been other functions that i have considered using a DAO dataset, just to avoid the need to create bunch of queries outside of my VBA.

PK

 

[Remou]

I think perhaps you should go for a maintenance mode that would open the back end exclusively and not only allow such things as updates to the special tables, but also provide for compact and repair.

http://lessthandot.com

 

[Remou]

Here is a link :

http://msdn.microsoft.com/en-us/library/aa221388(v=office.11).aspx

http://lessthandot.com