Break link to link database

Question

I work with two Access 2010 databases. One is stored on the file server of our company, and the second is stored locally on several PCs. I would like to save the VBA code in a network database and use this file as a reference library for local copies. However, with this configuration, the network file is locked for editing while the local copy is open. Using VBA, is it possible to break the connection between two files without closing the local file?

In an attempt to find a workaround, I create a test environment as follows:

• Two empty Access 2010 database files are created in C: \ DB Test \
  • Local DB.accdb
  • Network DB.accdb
• Added LocalCode module in Local DB.accdb
• Added RemoteCode module in Network DB.accdb
• Added link to Microsoft Visual Basic for application extensibility 5.3 in Local DB.accdb
• Added link to C: \ DB Test \ Network DB.accdb in Local DB.accdb
  • In this link, the Network Database has been added to the project list of my VBA editor, as if the file was open.
• Added the following procedure for the LocalCode module in Local DB.accdb

Public Sub ClearDBReference() Dim DBFile As String Dim Proj As VBIDE.VBProject Dim Ref As Access.Reference DBFile = "C:\DB Test\Network DB.accdb" For Each Ref In Application.References If Ref.FullPath = DBFile Then ' Successfully removes the library ' reference to the network database Application.References.Remove Ref Exit For End If Next For Each Proj In Application.VBE.VBProjects If Proj.FileName = DBFile Then ' Run-time error '440': Method 'Remove' ' of object '_VBProjects' failed Application.VBE.VBProjects.Remove Proj End If Next Set Ref = Nothing Set Proj = Nothing End Sub 

When I executed ClearDBReference , it successfully deleted the library link to C: \ DB Test \ Network DB.accdb , but could not delete the project for Network DB . It’s as if there is a ghost link between the two files, but I'm not sure what this causes or what to try next.

Answer 1

Not quite sure that I understand your scenario, but it looks like you are only referring to the Front-End (FE) application / database?

If so, you can create a stub application for local machines that simply copies the FE to the user computer each time it starts, opens the real FE application, and then the stub application closes.

Thus, the "master" on the server is never blocked. Of course, you will not modify this file, you will work on a dev-copy, and then replace the wizard on the server.

ps, "master" should be an accde file.

Answer 2

Is there a reason why both databases cannot sit on the server?

A slightly different scenario, but we always add Front End and Backend to the server with accdb - thus, there are no problems that arise due to a network outage. It also improves performance and eliminates the need to back up files on a local PC.