msaccess odbc to sqlserver 2005 lock:timeout

I've got msaccess database connecting to sql server via linked tables using "sql server" odbc driver. It mostly works well except to when it comes to transactions.

开发者_开发百科

The issue I can't figure a workaround for is the "Lock:timeout" that occurs when an msaccess form decides to refresh itself while a large transaction is being processed (10-30 seconds).

If msaccess attempts to hit a row that is locked by the transaction, the sql profiler shows up Lock:Timeout instantly but MSAccess fails to handle this, and locks up the application till its own querytimeout expires (60 secs). The stupid thing is, it also suspends the VBA so the transaction can't finish.

Apart from closing all forms that are open during these batches, what can I do?

If I could set the mssql lock timeout for the odbc connections, the transaction could have a chance to finish, but there doesn't seem to be a connection string parameter for this. It is tricky because MSAccess' background behaviour is mostly out of my control. And I'm pretty sure there is no global lock timeout that can be set for an sql database.

Any ideas? Thanks Luke

---

Instead of using the msaccess currentproject.connection, I start a new adodb connection directly to the SQL server then set a lock timeout. e.g. cnn.execute "set lock_timeout 1000". Then this connection will throw an error when the lock timeout expires - this error you can catch. The downside to this is you have to translate the jet sql into tsql. The upside is you may be able to put the translated tsql in a stored procedure and have it run a faster.

I found a few tricks to minimising the amount of locking msaccess does before you attempt to put a transaction through. Putting in a doevents before you starting a transaction helps - it lets control return to msaccess so it can catch up in any asynchronous refreshing it might be doing.

In another instance, I had a calculated column that took a long time to compute (3-5 seconds) after a me.refresh. I needed that me.refresh and was looking for a better solution than disabling the control. I discovered if just kept retrying the transaction after catching a locking error that it would working within 5 attempts so long as I had a doevents between each statement. I then discovered this putting this delay in would usually let msaccess finish doing it's thing.

tt = Timer: Do While Timer - tt < 5: DoEvents: Loop

The trouble with this is you don't know how long is long enough to wait. Because I don't like retrying the transaction over and over, I added a tolerable wait that is enough most of the time and when its not I throw the error back to the user to retry themselves.

I wish I had a way to get access to finish up, relinquish its locks and stay quiet for a time.

---

I found a better way was to send vba calculated results to a server-side table that isn't bound to any msaccess interface object, then call a "finalization" proc which uses that table to update other tables that might be bound.

Since all the time is in the vba and sending results to the server, the finalization proc is fast, so can be executed repeatedly until the locks are relinquished, with negligible client wait time.

Storing before and after data in the server-side table allows for detection of rows that might have been altered by another processes during the vba calculation. This avoids the need to lock anything for a long period of time that would increase the risk of msaccess freezing when it fails to handle the server's lock:timeout.

This msaccess freezing can still happen if a form refresh occurs during the finalisation proc's transaction (confirmed on 2k3 and 2k7 version) but it can't block this proc from completing so, at worse, will cause a minute delay to the client.