Break link to referenced database

Question

I'm working with two Access 2010 databases. One is kept on our company file server and the second one is saved locally on several PC's. I would like to store my VBA code in the network database and use that file as a reference library for the local copies. However, with that configuration, the network file is locked for editing as long as the local copy is open. Using VBA, is it possible to break the link between the two files without closing the local file?

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

1. Created two blank Access 2010 database files in C:\DB Test\
   • Local DB.accdb
   • Network DB.accdb
2. Added module LocalCode to Local DB.accdb
3. Added module RemoteCode to Network DB.accdb
4. Added a reference to Microsoft Visual Basic for Applications Extensibility 5.3 in Local DB.accdb
5. Added a reference to C:\DB Test\Network DB.accdb in Local DB.accdb
   • This reference added Network DB to the projects list of my VBA editor as though the file were open.
6. Added the following procedure to 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 successully removed the library reference to C:\DB Test\Network DB.accdb but was unable to remove the project for Network DB. It's as though there were a ghost link between the two files but I'm uncertain what is causing it or what to try next.

Answer 1

Not entirely sure I understand your scenario, but it sounds like you're only referring to the Front-End (FE) app/database?

If so, you can create a stub app for local machines, which just copies the FE to the user's machine each time it's run, opens the real FE app, and then the stub app shuts.

That way the 'master' on the server is never locked. Of course, you wouldn't modify this file, you'd work on a dev copy, then replace the master on the server.

ps, the 'master' should be an accde file.