Record locking for dummies...?

[DataMiner]

Hi,
Even though I've been developing access apps for years, I still feel like I need a book called "record locking for dummies", so please excuse if this sounds like a dumb question:

I have a db that I need to run a bunch of auto-updates on, multiple times per day. That same db is in use pretty much constantly by 4 users. The db is split; each user has their own copy of the fe, with a shared be. The updates effect only the data in the be tables.

I want to make sure that users do NOT make any edits to several specific tables while my auto-updates are running. The way I've done this up until now is to just force users out of the db while the updates are running. However, for various reasons, in this newest app, this is not working very well.

So, my question is: is there a better way? Can I somehow just "lock" specific backend tables temporarily so that they can't be edited? Can I temporarily lock the who backend?

I have a vague notion that action queries do some record locking automatically. But I really need to make sure a specific table stays locked to users through a series of action queries and dao.recordset manipulations.

Any pointers appreciated.

Thanks.

 

[ghudson]

I would force the users out of the db [or at least back to the main menu] when you are updating the records. There would be no way to prevent a locking issue if a user has a lock on a record or the entire table. You could not trust your data if the users are able to lock the records thus preventing all records from being updated.

 

[DataMiner]

Thanks for the advice. The direction I"m heading right now is to force users back to the main menu (as you suggested) instead of all the way out of the db.

I have code that will "un-dirty" any currently dirty records prior to running the updates. I was hoping that maybe if I did that, then somehow I could use record-locking to lock the whole table for the duration of the update, without actually having to force the user to close the form they use to for updating the table. Possible? What do you think?

 

[ghudson]

In my opinion... I would advise against running an update on the data source if a user is in the form with access to any records in that data source.