Keeping a shared Access Database available

I can’t Open My Database!

Who did this?… you may ask, well obviously it was user ‘Admin’.  Of course, that’s of little help since everyone who opens a database without user level login controls will be recorded as user ‘Admin’.  In this case, there’s a computer name to track down the offending user, however if you’re using a database from a terminal server there could be many people accessing the file from the same computer name as well.

Immediate Workaround

You will be able to open the database just by copying the database file to a new file name… however now you have 2 copies and are risking some nasty change problems.  You did split the database didn’t you?

Of course this is not a good long term solution so what else can you do?

Why does this happen?

This message will appear when any user anywhere has the database opened exclusively.  Well how did that happen without them specifically choosing to open it exclusive?  Easy, Access will automatically escalate the database to exclusive access whenever any design change occurs.  This can be accidentally initiated a number of ways, and apparently without the user knowing about it.

How do we stop that from happening?

Option one:Enable user level security so only administrators can initiate design changes on the database objects.  This is a working solution, but opens up a whole other can of worms and complications in using and designing the database.  Personally, unless you need users to login with a name and password, I’d leave this alone.

Option Two:Force the database into thinking there are others opening and using the database when it is opened.  Auto escalation only occurs when Access thinks only one person has the database open, so forcing a second connection at startup keeps Access from being able to force exclusive access.  This is the better solution for simple databases, since the code to accomplish this is small.

Public Function ForceShare()

‘forces second instance open to avoid automatic promotion to exclusive access

On Error GoTo trap

Static Conn2 As Object: Set Conn2 = CreateObject(“ADODB.Connection”)
Conn2.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & CurrentProject.FullName

Exit Function

trap:

MsgBox Err.Description

End Function

This function opens a connection to the current database from Visual Basic.  This appears to Access as a second user in the database, so no auto escalation for you!  Call this function from a macro named “AutoExec” to make it happen when the database is opened.  Note the connection string is specific to the version of Access, this function is valid for Access 2010 and 2013.

Now I can’t change the design on purpose!

Well that’s a little side effect to this method.  The solution is to use the shift key while opening the database to keep AutoExec and other startup options from running.  Then you’re free to fiddle with the design again at will.