Sharing a Microsoft Access reference with multiple databases

[drumahh]

I have created a "library" Access Database. I "reference" this library file within another Microsoft Access database by using Tools->References->Browse->
File Name: (Libraryfile.ac cdb)
Files of Type: Microsoft Access Databases (*.accdb)

This works as planned.

When I create a second Microsoft Access that references the same library, it works.

Unfortunately, it does NOT work when I open both applications at the same time. It doesn’t appear to share the “library file” (Libraryfile.ac cdb).

When I open the first app, the library file creates a .laccdb file. I’m assuming this is locking out the use of the library file when the second application tries to open and use the referenced Libraryfile.acc db.

Is there a way to share the library file among two applications running at the same time?
• This is not a case of splitting front end and back end functionality.
• My file options Client Settings are set to Shared
• I have tried putting the library file on my local machine and the Network location
• I am using Access 2010

[PhilOfWalton]

Your post interested me, as I use the same library database in all my projects (It deals with things like Switchboard menus, backups, Company information, special message boxes, and all sorts of frequently used functions)

I have never had a problem with multiple databases being open. Generally speaking the library database is in a main folder, and the other database is in their own folder.

However, I tried putting all that databases in the main folder, and still no problems.

Also using Access 2010 in Windows 10.

Is it significant that all my library database is in the same folder, and all the other Dbs are in that folder or subfolders?

Phil

[PhilOfWalton]

Slept on it and had a further thought.

My library database has no tables in it, only forms and lots & lots of VBA. The forms have lots of SELECT statements to populate RecordSources and RowSources for Combo Boxes etc.

All the Tables both for the main database and the forms & "SELECT" statements in the library database are held in the main database (usually as linked tables, but that is not relevant)

Now this is relevant.
Here is the code for the OnOpen for example a form called Modifications (Where I track database versions / modifications) in the library database.

Private Sub Form_Open(Cancel As Integer)

    Dim MyDb As Database
    Dim SQLStg As String
    
    Set MyDb = GetDb
    
    SQLStg = "SELECT Modifications.* "
    SQLStg = SQLStg & "FROM Modifications "
    SQLStg = SQLStg & "IN '" & MyDb.Name & "' "
    SQLStg = SQLStg & "ORDER BY ModDate;"

    Me.RecordSource = SQLStg
    
End Sub

This GetDb is crucial and gets the name of the main database.

Dim wrkJet As Workspace
Global ExternalDBName As String        ' Calling Db

Function GetDb(Optional Args As String) As Database
    
    Dim ExternalDb As Database
    
    On Error GoTo GetDb_Error
    
    If ExternalDBName = "" Then                         ' we have a name
        If Nz(Args) > "" Then                           ' Database name passed
            ExternalDBName = Mnu_strDField(Args, "~", 2)    ' Second string
            GoTo SetExternal
        End If
    End If
    
ReOpen:
    ExternalDBName = CurrentDb.Name
    
SetExternal:
    'Create Microsoft Jet Workspace object.
    Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
  
    'Open Database object from saved Microsoft Jet database for exclusive use
    Set ExternalDb = wrkJet.OpenDatabase(ExternalDBName)
    
    Set GetDb = ExternalDb
    Set ExternalDb = Nothing
    Exit Function

GetDb_Error:
    If Err = 3059 Then                  ' Error "Cancelled by user
        Resume ReOpen
    End If
    If Err = 3078 Then              ' Can't find CoInfoPaths in current database
        ExternalDBName = CurrentDb.Name
        Resume SetExternal
    Else
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure GetDb of Module FindFiles"
    End If
    
End Function

You need this, though in practise, I'm not sure it's used as I don't think I pass any arguments .. but I must have had a reason I suppose. Equally I think the ExternalDbName is always blank, so again superfluous code, but if it ain't broke, don't fix it.

Public Function Mnu_strDField(MyText As String, Delim As String, GroupNum As Integer) As String

   ' Returns a group extract from a string via a delimter.
   ' Hence to grab "cat" from the string dog-cat  you get:
   ' Mnu_strDField("dog-cat","-",2) = "cat"

   Dim StartPos As Integer, EndPos As Integer
   Dim GroupPtr As Integer, Chptr As Integer
   
   Chptr = 1
   StartPos = 0
    For GroupPtr = 1 To GroupNum - 1
       Chptr = InStr(Chptr, MyText, Delim)
       If Chptr = 0 Then
          Mnu_strDField = ""
          Exit Function
       Else
          Chptr = Chptr + 1
       End If
    Next GroupPtr
   StartPos = Chptr
   If Mid(MyText, StartPos, 1) = Delim Then
      Mnu_strDField = ""
   Else
      EndPos = InStr(StartPos + 1, MyText, Delim)
      If EndPos = 0 Then
         EndPos = Len(MyText) + 1
      End If
      Mnu_strDField = Mid$(MyText, StartPos, EndPos - StartPos)
   End If

End Function

So basically, is the library database accessing the correct database tables otherwise it may be trying to access the same table twice in the same database, because it is unaware that you have opened a second database.

Phil
Phil

[drumahh]

Thank you for your reply. I think you are on to something. I used a very simple "HelloWorld " library which has a function that returns a "Hello World". I created 2 apps that call it and they both open without issue.

I will see where I can make references to other tables/Select statements in the prior database and see if it may be causing the lock issues.

Thanks, I'll try and let you know

[PhilOfWalton]

As a matter of interest, what are you hoping to achieve with your library database?

Have spent a lot of time developing my library database - Utilities.AccDb , and to be more specific about it's purpose, it covers many of the basics used in all databases.
So forms include:-
Company information (including paths to back end databases, pictures, storage for PDFs etc)
Menus - Switchboard type (I use a treeview type menu) and various forms to set this up and allow which portion of the menu, various users can see.
Backup and checks whether they are done regularly
Compact BE database
Magnifying Glass,
Library database paths
Rich Text message box (instead of the standard MsgBox)
Among the VBA routines are ones to open various dialogue boxes to find files, open font & colour dialogue boxes, check whether forms are loaded, Geocoding to check addresses, etc.

I Also use Peter's Software "ShrinkerStretc her" which is very good product for shrinking and strething forms and keeps everything in proportion as you shrink a form or report. That is a library database of my Utilities database, so in practice my main database has a reference to the Utilities database, and that has a reference to the ShrinkerStretch er database. 3 Deep. Is there a limit?

Phil

[drumahh]

My hope is not only to include one library database in our main applications, but multiple library databases with distinct functionality. For example, common file writing techniques, imports and exports from Excel or PDF, common SQL routines, logging routines, common open and close Access recordset and query routines, common logon methods etc.

If it works out, it will really streamline our existing and future applications.

[PhilOfWalton]

Although your suggestion should work, I think you should be aware that AFIK, Access searches for a function name, first in the main database, then in a referenced database. I have no problems because my library DBs are "In Series"

From what you are saying your library DBs will be "In Parallel". So providing each function name is unique throughout all your databases, this shouldn't be a problem.

The other potential problem is that your library DBs will all use common routines like checking that paths exist for import / export routines but will use an identical routine for writing a file. I think these routines will have to be written and maintained in each of your library databases, and ensure that the identical function have a different name in each library DB.

That is why I would advocate using a single library DB, and when compiling it, you will get errors of duplicate function names.

If you are concerned about size, my Utilities.AccDb is 5.7MB (purely forms & modules) that represents a lot of years of work.

Phil

[drumahh]

So I found the specific locking issue. In my library database, I have a linked SharePoint list. When I delete the link to the list, I can open up both applications that reference the same library file.

So, it is indeed related to "table" access. So now I need to figure out if the way I'm linking the SharePoint list can be modified so that it is "shared" - you'd think SharePoint could "share" :)

[drumahh]

Very happy, although somewhat disconcerting about Microsoft's obscure features. Within my library database I had to set an option for SharePoint caching, which I would not have thought of. But I have successfully referenced two library access databases and opened them using two apps, so it appears to be a go.

I found the answer here:

http://www.access-programmers.co.uk/forums/showthread.php?t=214875

From the File option on the Ribbon, select the Options option and then select the "Current Database" option. The last option at the bottom of that page is the "Cashing Web Service and SharePoint tables" check box. By default, this option is checked. Clear both of the sub-options and then uncheck the "Cashing Web Service and SharePoint tables" option. That will stop the random record locking and the other weird data related issues.

[zmbd]

just to clarify this situation:

Is your "library" the SharePoint lists or another database file? This is a very important distinction as the SharePoint environment places a lot of restrictions and requirements that "traditiona l" front/backend databases do not need in order to properly function.

[drumahh]

The Microsoft Access database file which serves as my library contains "linked" SharePoint lists. Within that library, I read and write to those SharePoint lists via Microsoft Access functionality.

I then link that library file via the "reference" method to two other Microsoft Access applications, and call the public functions within the library, which read and write to the SharePoint lists.