Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Is there a way to list users accessing same record?
- Thread starter mutidjo
- Start date
SQL Server is quite good about handling deadlocks.
Are you using pessimistic locking?
Are the updates happening from a Form that diplays the records for the user to update.
What is the scenario where the lock is happening?
Is the record part of a transaction where other records are being updated?
What level of graularity on the locks - record, batch?
Are you using pessimistic locking?
Are the updates happening from a Form that diplays the records for the user to update.
What is the scenario where the lock is happening?
Is the record part of a transaction where other records are being updated?
What level of graularity on the locks - record, batch?
- Thread starter
- #3
Thanks for your reply.
I'm not sure what kind of locking is involved, since the recordsource is a SQL Server table. I was assuming it was optimistic, or the default?
Yes, the updates are happening from a form that displays the records for the user to update.
I have a main form, within it is a subform. There are 3 users who have the main form open and are reading the same record. Here are the sequence of events:
1. User1 is just viewing the records.
2. User2 updated a field in the main form.
3. User3 updated a field in the subform which triggered an update of a field in the main form. User3 received write conflict message box, which gave 3 options to save record, cancel and drop changes. When User3 tried to click the buttons, nothing happened, and received an OLE error message, and the write conflict box keeps popping up several times. Finally User3 is able to get out, but when opening the main form again, notices that the field he has just updated is not saved.
Any ideas, how I can resolve this issue?
I'm not sure what kind of locking is involved, since the recordsource is a SQL Server table. I was assuming it was optimistic, or the default?
Yes, the updates are happening from a form that displays the records for the user to update.
I have a main form, within it is a subform. There are 3 users who have the main form open and are reading the same record. Here are the sequence of events:
1. User1 is just viewing the records.
2. User2 updated a field in the main form.
3. User3 updated a field in the subform which triggered an update of a field in the main form. User3 received write conflict message box, which gave 3 options to save record, cancel and drop changes. When User3 tried to click the buttons, nothing happened, and received an OLE error message, and the write conflict box keeps popping up several times. Finally User3 is able to get out, but when opening the main form again, notices that the field he has just updated is not saved.
Any ideas, how I can resolve this issue?
When you say trigger is that an SQL Server trigger that is updating the table?
You can check the lock type on the Form by looking at the lock property under the data tab.
With optimistic locking the lock should be acquired just before the update. Probably what is happening is that user3 is trying to update a record that user2 updated after user3 read the record but prior to user3 doing the update. It is normal for SQL Server to inform the user that the record has been updated since they last read it. Access does not appear to offer a graceful alternative for the user. Maybe you could trap that specific error and just cancel the update.
You can check the lock type on the Form by looking at the lock property under the data tab.
With optimistic locking the lock should be acquired just before the update. Probably what is happening is that user3 is trying to update a record that user2 updated after user3 read the record but prior to user3 doing the update. It is normal for SQL Server to inform the user that the record has been updated since they last read it. Access does not appear to offer a graceful alternative for the user. Maybe you could trap that specific error and just cancel the update.
- Thread starter
- #5
thanks for the input...
i trapped the specific error, but was unable to cancel the update. Do you know how I could do that?
Now i have it so that it traps the error, but it goes ahead and does the update, but I'm still having the same problems, adn don't know how to cancel the update, without displaying the default Write conflict box.
I used Response = acDataErrContinue to go ahead with the update since didn't know hot cancel it.
Many thanks for your help.
i trapped the specific error, but was unable to cancel the update. Do you know how I could do that?
Now i have it so that it traps the error, but it goes ahead and does the update, but I'm still having the same problems, adn don't know how to cancel the update, without displaying the default Write conflict box.
I used Response = acDataErrContinue to go ahead with the update since didn't know hot cancel it.
Many thanks for your help.
Just to back up a little and review the design. Are you trying to keep 2 or more records in sync? If so, are they part of a transaction? Can you explain the logical transaction that you are trying to accomplish? It could be a design issue that is causing you the problem since normally another user should not be editing the record that is part of the transaction.
- Thread starter
- #7
Hi,
Thanks for your reply. I'll try to explain the transaction as best as i can.
I'm using optimistic locking.
User2 makes an update transaction on Field 1 of Record1 in Table A.
User3 then makes an update transaction on Record1 in Table B, which programmatically in the application code will update Field2, Field 3, and Field 4 of Record1 in Table A.
There are no triggers involved since in the application, when a user updates a field in the subform it automatically inserts some data in the main form (updates information). So yes, the subform data and the Main form data is in sunc with each other.
Thanks in advance.
Thanks for your reply. I'll try to explain the transaction as best as i can.
I'm using optimistic locking.
User2 makes an update transaction on Field 1 of Record1 in Table A.
User3 then makes an update transaction on Record1 in Table B, which programmatically in the application code will update Field2, Field 3, and Field 4 of Record1 in Table A.
There are no triggers involved since in the application, when a user updates a field in the subform it automatically inserts some data in the main form (updates information). So yes, the subform data and the Main form data is in sunc with each other.
Thanks in advance.
It seems to me the data must be unnormalized.
Access works well with normalized data and the built in features expect the data to be normalized. When you deviate from having normalized data, then you run into situations that Access does not handle well in the generated Forms.
In a typical one to many design, like a Order and OrderDetails. The main form would have the order data which would be added first and the subform would have the order detail which would be added line by line. The records in each table are related but also independent and can stand by themselves. So, for example, if a detail line record is rolled back because of an update conflict it does not throw the order out of sync. If you want the total for the order you add up the details. Now, if for some reason, you kept a quantity field for all the details in the order record, then you need to keep the order in sync. with the details and have caused the data to be unnormalized in this instance. This is not a good design and requires a lot of extra coding in access to make both records a logical transaction. This is best to avoid unless you want to do the coding and maintain the transaction yourself.
If you are doing this type of thing, then it may be better to redesign so that the coding in access can be kept simple.
Access works well with normalized data and the built in features expect the data to be normalized. When you deviate from having normalized data, then you run into situations that Access does not handle well in the generated Forms.
In a typical one to many design, like a Order and OrderDetails. The main form would have the order data which would be added first and the subform would have the order detail which would be added line by line. The records in each table are related but also independent and can stand by themselves. So, for example, if a detail line record is rolled back because of an update conflict it does not throw the order out of sync. If you want the total for the order you add up the details. Now, if for some reason, you kept a quantity field for all the details in the order record, then you need to keep the order in sync. with the details and have caused the data to be unnormalized in this instance. This is not a good design and requires a lot of extra coding in access to make both records a logical transaction. This is best to avoid unless you want to do the coding and maintain the transaction yourself.
If you are doing this type of thing, then it may be better to redesign so that the coding in access can be kept simple.
- Status
Similar threads
- Locked
- Solved
- Locked
- Question
- Locked
- Question
- Locked
- Question
