Maintaining Referential Integrity
-
I'm pretty new to ADO and need some guidence. I'm using MS Access 2K. I have three tables. The tables looks as follows: UserAccounts ------------ UserAcctID (key, autonumber) UserAcctData Queues ------ QueueID (key, autonumber) QueueName QueueRights ----------- UserAcctID (foreign key) QueueID (foreign key) Rights I'm using ADO and DCOM so there's potential of multiple admin users trying to access and modify the tables at one time. I need to ensure that referential integrity is ensured, and wondered if developers typically use locking mechanisms or the referential integrity tool in MS Access, or both. I also, have the following situations, and am not sure of the best way to resolve them in terms of locking and ensuring that the keys/data won't be out of sync: 1) When one admin is editing another user's account, another admin cannot edit that user's account. What's the best way to lock the account? Add a lock timestamp column to UserAccounts? Also, is this a user-friendly restriction? Do other administration program lock another user from editing the same account, or do they allow them to edit it and then notify them that it has changed when it's time to update and for them to reload and redo what they've done? 2) When an admin is about to update another user's queue rights, I would need to lock the entire "Queues" table from being added to while records are being added or updated to the QueueRights table. What's the best way of accomplishing that? 3) Also are there any good websites and/or books that discusses how to resolve issues with multiple users and databases? Thanks in advance for any help!