I've migrated an Access 2007 database to a SQL Server 2008 R2 backend, retaining the Access front end with linked tables. Performance was fine with a few users.
Yesterday the application was opened up to multiple users and in the afternoon the performance went to hell. Took literally minutes for the front end to open or close, or do anything. The delay in closing seemed particularly strange, as it's done via a button on the main menu that just calls Application.Quit. Doesn't do anything else at all.
I found the old LDBView utility seemed to work on the .laccdb file. It was showing 11 users logged on plus another 15 that were no longer logged in. It didn't show user names, just the machine names they were connecting from. Mostly those machine names were two virtual hosts so I don't know if they were actually different users or the same user being reported multiple times (don't know enough about the format of .laccdb files to be able to tell).
I took a copy of the troublesome Access front end file (the .accdb file) and ran the copy from the same network location as the original. It ran fine. So the problem does not seem to be in the network, or in the SQL Server database, or that the Access front end is corrupt somehow.
Eliminating those possibilities, the problem seems to be related to multiple users using the same Access front end simultaneously. The original Access database, before the tables were migrated to SQL Server, worked fine for the same number of users. So the fundamental design of the front end seems to be able to support multiple users.
How would I go about tracking down the source of contention issues in the Access front end? To me it seems there is no visibility of what is going on, apart from viewing the number of concurrent users in the .laccdb file.
By the way, since the original Access database worked fine with multiple users, I've been told there is no way each user can have their own copy of the Access front end, as it would be a maintenance nightmare.
