Hi,

My database is in a multiuser environment. Once a week there is an update to a table of a few hundred records this takes a couple of minutes. So I would like to lock the table/records while this is happening.

What is the best way to do this, can it be done thru the line below or something like it?

Set rs1 = db.OpenRecordset("MyTable", , dbDenyWrite, dbPessimistic)

Or would this only lock the table "MyTable"?

TIA
Martin

If you're running the process via form then the RecordLocks property would be a better option.

Forms("YourForm").RecordLocks = 1

Edited by: PaulBrand on Thu Dec 10 7:41:36 EST 2009.

well - yes, no, maybe; kind of depending on what you really are trying to do....

in the most general sense the normal updating of values in a db doesn't require any locking at all... so if it routine updating of data and your are guessing/assuming there will be conflicts - there is nothing to sweat....

if you are in a situation where no one should be able to use the table until it is updated - - - maybe with a new price list for example...you have a management dilemma as before locking a table you have to first kick people out of it....and there is no way to kick people out of a table...and if the BE's .ldb is there - - there is no easy way to kick people off the BE in general....but this as much a management issue as a technical issue as to when changes must occur vs people active in the db.......and I'm not sure if this is your scenario so this is just speculation....

you could do something sexy like having a field in the table named Locking....and if it is value 2 - then any form opening in the FEs looks at Locking field and If =2 - then locks the relevant form controls.... you could manipulate between value 1 and 2 easily by doing a mass Update Query to the table. This doesn't resolve people with opened forms at the time you change to value 2 however....