Locking Errors

[max1mum]

Ever Since i began using transactions on a Normalized DB, i have had many issues with "Cannot Update Current Locked by %username% on %machinename%" . What I am doing is using a unbound formand compiling the SQL statement from the form values to update/Insert data into the tables. I am having to compile a few different SQL statements for each table. This is only happening in a multi User environment.

I call a function to compile the SQL statements so they would look like this when they come back

UPDATE REPAIRS SET [Test]='PASS'
UPDATE SHIPPING SET [Ship Date]=#1/1/2003#,etc etc.
I then assign that to a appropriate variable, ShippingSQL ,RepairSQL in this case.

Then i begin my transaction with:

cnn.BeginTrans
cnn.Execute(RepairSQL)
cnn.Execute(ShippingSQL)
cnn.CommitTrans


would it be better to work with a recordset rather than executing SQL commands? Just looking for suggestions here. Im stuck. Thanks A lot.

- Max1mum




Max1mum.
- VBA Programmer
- Studying for MCSD

 

[RickSpr]

This is normal in a busy multi-user environment. When the update occurs as part of the Access form logic (as opposed to in VBA code), Access waits for a short time and tries again. The retry wait interval and number of retries are set in the Options dialog, Advanced tab ("Update retry interval" and "Number of update retries&quot.

You can minimize the incidence of lock conflicts by making sure your users all use forms with Record Locking = No Locks. However, this has consequences in the user interface, so read up on it before you make this change.

If you're using Access 2000 or later, you can reduce the incidence of conflicts by making sure all users use Row Level Locking, so that only the current record is locked instead of a whole page of records.

If these measures don't solve the problem, you'll have to implement your own retry loop in your VBA code. You might try calling a common subroutine that implements the retry logic, passing it the SQL string to execute. The subroutine could retrieve the Access option settings using the GetOption() method with the option strings "Update Retry Interval (Msec)" and "Number of Update Retries&quot. To wait for the retry interval, it can call the Windows API Sleep() function.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[max1mum]

RickSpr,
Thanks for your response. I have implemented a retry loop and it ran a lot smoother, but it would still lock users out if it was in the middle of a transaction and wouldnt allow user that were already in the DB to perform a update until the other transaction had finished. It appears that once i Initiated a transaction everything gets locked down.This is probably due to my use of CurrentProject.Connection, but if i define a Provider and Connection string it runs real slow. So for the mean time I have taken out the transactions and Cnn.Execute commands and now I am just using DoCmd.RunSQL. With DoCmd its not as fail proof, but the users are able to Insert/Update data w/out any problems. Time for MSDE

- Max1mum



Max1mum.
- VBA Programmer
- Slackin on Studying for MCAD