Avoid Interleaving Transactions

[TastyWheat]

Is there a special way I can execute two transactions (a read and a write) without interruption from other transactions/users?

I'm implementing my own autonumber system so that the numbers rollover after 4 digits. Before you sound the alarm, I'm not using this as my primary key, it's just an ID that's used around the office. At first I wasn't worried about race conditions (e.g. two users trying to get an autonumber at the same time) but now I am. I want to make sure my code performs a SELECT then an UPDATE back-to-back without any other transactions (on that record) occuring in between.

 

[ajetrumpet]

Is there a special way I can execute two transactions (a read and a write) without interruption from other transactions/users?

Not quite following you here...

I want to make sure my code performs a SELECT then an UPDATE back-to-back without any other transactions (on that record) occuring in between.

code cannot perform non-action queries like a SELECT, so this may not work for you. the SELECT "sql keyword" can only be used in VBA to specify data, not actually to select and do something with it.

As for "pausing" while your code is running, maybe you could change the editing properties of the form within the first line of code, and then set them back to the original setting on the last line. Do you think that's a good idea?

 

[chergh]

Your wanting to use a recordset object here. Have a look at "lockedits" property in the help files in the VBE if you are using DAO or the "locktype" property if your using ADO. Using pessimistic locking will lock the record as soon as you use the edit method of the recordset.

 

[gemma-the-husky]

the approved way to do this is to, at the time you are saving the record, and need the number

- lock a "number table"
- retrieve the next number from that table
- increment the number
- unlock the table
,
-note that yuo should get the number at the time of saving therecord - if you get it earlier, and scrap the edit you will lose the allocated number from your sequence

 

[TastyWheat]

Your wanting to use a recordset object here. Have a look at "lockedits" property in the help files in the VBE if you are using DAO or the "locktype" property if your using ADO. Using pessimistic locking will lock the record as soon as you use the edit method of the recordset.

I saw that option but I wasn't sure if it would really work the way I want it to. Is there any other way to explicitly lock a table?