By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,060 Members | 1,893 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,060 IT Pros & Developers. It's quick & easy.

Sharing a Microsoft Access reference with multiple databases

P: 16
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.accdb)
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.accdb).

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.accdb.

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
Jun 7 '16 #1
Share this Question
Share on Google+
10 Replies


PhilOfWalton
Expert 100+
P: 1,430
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
Jun 7 '16 #2

PhilOfWalton
Expert 100+
P: 1,430
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.  
  3.     Dim MyDb As Database
  4.     Dim SQLStg As String
  5.  
  6.     Set MyDb = GetDb
  7.  
  8.     SQLStg = "SELECT Modifications.* "
  9.     SQLStg = SQLStg & "FROM Modifications "
  10.     SQLStg = SQLStg & "IN '" & MyDb.Name & "' "
  11.     SQLStg = SQLStg & "ORDER BY ModDate;"
  12.  
  13.     Me.RecordSource = SQLStg
  14.  
  15. End Sub
  16.  
This GetDb is crucial and gets the name of the main database.

Expand|Select|Wrap|Line Numbers
  1. Dim wrkJet As Workspace
  2. Global ExternalDBName As String        ' Calling Db
  3.  
  4. Function GetDb(Optional Args As String) As Database
  5.  
  6.     Dim ExternalDb As Database
  7.  
  8.     On Error GoTo GetDb_Error
  9.  
  10.     If ExternalDBName = "" Then                         ' we have a name
  11.         If Nz(Args) > "" Then                           ' Database name passed
  12.             ExternalDBName = Mnu_strDField(Args, "~", 2)    ' Second string
  13.             GoTo SetExternal
  14.         End If
  15.     End If
  16.  
  17. ReOpen:
  18.     ExternalDBName = CurrentDb.Name
  19.  
  20. SetExternal:
  21.     'Create Microsoft Jet Workspace object.
  22.     Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
  23.  
  24.     'Open Database object from saved Microsoft Jet database for exclusive use
  25.     Set ExternalDb = wrkJet.OpenDatabase(ExternalDBName)
  26.  
  27.     Set GetDb = ExternalDb
  28.     Set ExternalDb = Nothing
  29.     Exit Function
  30.  
  31. GetDb_Error:
  32.     If Err = 3059 Then                  ' Error "Cancelled by user
  33.         Resume ReOpen
  34.     End If
  35.     If Err = 3078 Then              ' Can't find CoInfoPaths in current database
  36.         ExternalDBName = CurrentDb.Name
  37.         Resume SetExternal
  38.     Else
  39.         MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure GetDb of Module FindFiles"
  40.     End If
  41.  
  42. End Function
  43.  
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.

Expand|Select|Wrap|Line Numbers
  1. Public Function Mnu_strDField(MyText As String, Delim As String, GroupNum As Integer) As String
  2.  
  3.    ' Returns a group extract from a string via a delimter.
  4.    ' Hence to grab "cat" from the string dog-cat  you get:
  5.    ' Mnu_strDField("dog-cat","-",2) = "cat"
  6.  
  7.    Dim StartPos As Integer, EndPos As Integer
  8.    Dim GroupPtr As Integer, Chptr As Integer
  9.  
  10.    Chptr = 1
  11.    StartPos = 0
  12.     For GroupPtr = 1 To GroupNum - 1
  13.        Chptr = InStr(Chptr, MyText, Delim)
  14.        If Chptr = 0 Then
  15.           Mnu_strDField = ""
  16.           Exit Function
  17.        Else
  18.           Chptr = Chptr + 1
  19.        End If
  20.     Next GroupPtr
  21.    StartPos = Chptr
  22.    If Mid(MyText, StartPos, 1) = Delim Then
  23.       Mnu_strDField = ""
  24.    Else
  25.       EndPos = InStr(StartPos + 1, MyText, Delim)
  26.       If EndPos = 0 Then
  27.          EndPos = Len(MyText) + 1
  28.       End If
  29.       Mnu_strDField = Mid$(MyText, StartPos, EndPos - StartPos)
  30.    End If
  31.  
  32. End Function
  33.  
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
Jun 8 '16 #3

P: 16
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
Jun 8 '16 #4

PhilOfWalton
Expert 100+
P: 1,430
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 "ShrinkerStretcher" 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 ShrinkerStretcher database. 3 Deep. Is there a limit?

Phil
Jun 8 '16 #5

P: 16
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.
Jun 8 '16 #6

PhilOfWalton
Expert 100+
P: 1,430
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
Jun 8 '16 #7

P: 16
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" :)
Jun 8 '16 #8

P: 16
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/...d.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.
Jun 8 '16 #9

zmbd
Expert Mod 5K+
P: 5,397
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 "traditional" front/backend databases do not need in order to properly function.
Jun 10 '16 #10

P: 16
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.
Jun 10 '16 #11

Post your reply

Sign in to post your reply or Sign up for a free account.