Hi all, I have a problem with a customer with a software written in Access. All the code and forms are inside a MDB file (MS Access 2003), the tables are linked with ODBC to a SQL Server 2008 R2. There're many forms in the application, some just visualize data from linked tables or views, others allow the user to edit the data too.




Sometimes, but randomly, the users get an error message opening some forms, errors about timeouts of the connection. After some troubleshooting I could manage to isolate that the error is due to a sql process that locks in some way some rows of a table. The particular wait_type of the process that hangs is ASYNC_NETWORK_IO. I read in many forums that this particular lock is due to the slow processing of data by the client, and the client in this situation is Access of course.


Now, I don't know if Access is responsible for this kind of lock, the only thing I'm sure is that when I kill the hanging process SQL Server and the Access application start to run normally. If this is the issue, I can't find a solution, as I can't change the isolation level of the connession from MS Access to SQL Server, and I don't know if I can change the locking strategy of MS Access itself. I have tried to move to MS Access 2010 or 2013, but I see that the linked tables are always made using ODBC, so I don't think changing the version of Access can improve the situation.


Does anybody know of what I am talking about? Could you suggest something to try to avoid this locks?


Thank you in advance for your help


Sebastiano