Our data resides in numerous MS SQL Server 2008 R2 databases. Data is accessed via various MS Access 2003 front end databases. The 2 most common are for managing customer demographic information and the other is for processing customer orders. We have approximately 15 to 20 users who are accessing this data at any given time. The front end databases are located on each user's workstation.
The problem: If one of the front end databases on a single workstation locks up, it takes down the entire SQL server and locks all other users out of the data. This occurs about twice per day on average. Some days not at all and other days more frequently. When this occurs, all other users get the spinning wheel of death and cannot access any data.
The lock ups have occurred on multiple computers and are usually limited to the 2 front end modules mentioned above. Sometime it is evident which computer caused the problem because an error message is displayed on the user's screen - usually a failed VBA routine, not always the same one, or an ODBC connection error. At other times, the culprit cannot be identified.
The problem can be corrected by closing the front end database that caused the problem. Upon doing so, all other computers free up and can then access the data. If the culprit cannot be identified, then by having all users close their open databases, the problem is corrected. There doesn't seem to be any commonality to the processes that are taking place on these workstations when the problem occurs. Both front end databases are large and contain a lot of functionality - they are 29MB and 25MB respectively after a compact and repair.
Thinking that either database might be corrupt, I've tried compacting and repairing, and creating a new database container and importing the components. All to no avail.