Symtom: When multiple users open the same MS-Access MDE (or MDB) file (or when a single user opens multiple instances of the same MDE file), the following error can occur:
Software: Microsoft Access 2003.
Cause: This error can occur when the database is automatically temporarily "promoted" from share mode to exclusive mode. This may happen when the VBA program changes something that Access wants to store within the MDE file, e.g. toolbar/menubar items (through Application.CommandBars).
Solution: The automatic promotion from shared mode to exclusive mode seems to occur only when the database is opened once. When the database is open more than once, the promotion does not occur. To prevent the locking effect from happening, the program can open it's own program database a second time using VBA, just at the start of the program, and keep it open.
I found that opening the program MDB/MDE (a second time) through ADO seems to be the best solution. Opening though DAO within the default workspace has unwanted side effects on the MS-Access application. Opening through DAO within a separate workspace has no effect, i.e. it does not solve the problem.Example:
Public Function Autoexec()
Static Conn2 As Object: Set Conn2 = CreateObject("ADODB.Connection")
Conn2.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & CurrentProject.FullName
Another solution is to use a Windows batch file to copy the program MDB/MDE to the user's temp directory and start the program from there. The users then have to run this batch file to start the program, instead of opening the MDB/MDE file directly. That way, each user gets his or her own copy of the program MDB/MDE and the error does not occur.
Author: Christian d'Heureuse (www.source-code.biz, www.inventec.ch/chdh)