I wrote several (I believe it's currently 7) Microsoft Access 2003 applications that people run. The applications are linked to tables in a 4.5 GB SQL Server database running in SQL Server 2008 Express. Five Access applications are local to each computer, and two are run from a server. They all (local and server based applications) use the same system.mdw file. There are four terminal servers, each with up to 30 users and about 60 stand alone computers. Most users use more than one Access application at a time, and some of them run more than one instance of the same application concurrently.
I converted our Access 97 applications to Access 2003 applications over 5 years ago. Although all the applications are in Access 2003 format, the system.mdw file is still in Access 97 format. This is because Access 2003 wouldn't convert this file. The system.mdw file works fine in Access 97 format and I am able to manage users in this file using Access 2003.
The past 3 business days users have been intermittently getting the error "Cannot Open the Microsoft Jet Engine Workgroup" when trying to run an Access application. One user is able to run an Access application using the same system.mdw file while another user can not. So far, if a user with this problem gets out of all the access applications that he is running, this has fixed the problem and he can then get into the applications without this message. This is a big problem if it occurs on one of the terminal servers because up to 30 users have to all get out of all of the access applications that they are running to fix this problem.
I searched for a fix to this problem and I read that it is a sign that the workgroup file is corrupt when you get this error message. I don't believe that is the case because other people using other computers can get into the access programs just fine using the same workgroup file. Last night I repaired and compacted the system.mdw file in Access 97. It dramtically reduced the size from 116,000 KB to 226 KB, but this morning a couple of users still had the same problem. I looked at the list of Open Files on the Server 2008 computer that contains the system.mdw file and saw that system.mdw was opened 190 times and system.ldb was opened 150 times.
What could cause this problem? Am I reaching a limitation for how many users can open the workgroup file concurrently? I haven't read about any such limitation, but a week ago people started using another access program that I wrote which causes the system.mdw file to be opened even more. Would it help to make a copy of the system.mdw file and set some of the programs or some of the users to use the copy so there aren't so many people all using the same workgroup file?