Security and record locking

[Simmo2010]

Hi All,

Hope this thread is in the correct place! I am fairly new to Access but have lots of SQL/VB experience (I am using Access here purely for the size of the app).

My question is one of best practice/easy to implement! I have a system I have created for around 5 users. I have split the DB tables to a server based location and then a client app with linked tables and forms for each user. I am now wondering (it is not live) how best to handle locking for the multiple user scenario. Can anyone advise?

Thanks

Simmo

 

[The_Doc_Man]

First, search this forum for "Record Lock" as a topic. You should get lots of hits.

Second, you have a couple of choices on style and some choices as to when you lock.

For read-only forms where all you do is look things up, I might suggest setting the lock management of that form to NO LOCKS. For everything else, I might prefer to use LOCK RECORD (as opposed to LOCK TABLE). And for a sharing situation, use OPTIMISTIC locking - which minimizes exposure of the locks to other interactions. Only use the PESSIMISTIC locking set up on your recordsets if you have a fear of destructive interference occurring between two users. That would happen if your algorithm is not simple "update" but is instead "select" "compute" "update"