Error 3734 (share mode error) appearing in error (1 Viewer)

guy-

New member
Local time
Today, 11:40
Joined
Sep 26, 2011
Messages
2
Hi guys, first post here so be gentle :)

I've inherited a half-complete access database and have very little knowledge of VBA, still very much a beginner. I'm feeling my way around the database and learning things as they come along but have come across something that urgently needs fixing - I have discovered a solution for it on the web but need some clarification before I pull the trigger.

The problem isn't an error as such, it's that the database is being automatically temporarily "promoted" from share mode to exclusive mode from one of the modules already in there - this is an issue as we have two people using the database at once and that's no good if one of them is getting locked out!

Anyway I found the following solution online:

How to avoid error 3734 ("The database has been placed in a state by user <name> on machine <name> that prevents it from being opened or locked")

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:

The database has been placed in a state by user <name> on machine <name> that prevents it from being opened or locked. (Error 3734)
This error occurs when the new passive shutdown/connection control is being used. This error message indicates that a user set the database in a mode that will prevent other users from opening it.
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:
Code:
Public Function Autoexec()
   Static Conn2 As Object: Set Conn2 = CreateObject("ADODB.Connection")
   Conn2.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & CurrentProject.FullName
   '...
   End Sub

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.

My question is whether I can simply paste the code into a module and let it do its thing, or whether I will have to edit it at all. My thinking is that the "CurrentProject.Fullname" bit will refer the database to itself and it therefore doesn't need to be changed, but is this correct? And am I right in thinking it will start automatically on opening the database?

As you can probably tell, I am a real noob. Any help would be much appreciated :)
 
Last edited:

Guus2005

AWF VIP
Local time
Today, 12:40
Joined
Jun 26, 2007
Messages
2,645
Don't know if you can execute the code and what it does. I am seeing only a fraction of it. I am curious what's behind the '...

The code you provided can be executed anytime. It does nothing really. It creates a connection and drops it because the function ends.

The Autoexec() function will not be executed by default. De Autoexec macro will! In the macro you can determine what to start, code or form.
In the menu Tools|Startup you can determine what form to open on database startup.

HTH:D
 

guy-

New member
Local time
Today, 11:40
Joined
Sep 26, 2011
Messages
2
After further pondering I think I've worked out what the ... is about. I guess what i'm meant to do is to find the piece of code which elevates the database to exclusive mode and use this code on either side to stop it from doing so.

The question now is, which piece of code does this?! I think I’m in for a bit of work trying to find out… there are 36 modules and 18 classes in this database :-o

Any idea what kind of thing I should be looking for anyone?
 

Users who are viewing this thread

Top Bottom