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
10 2843
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
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
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
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
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.
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
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" :)
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. zmbd 5,501
Expert Mod 4TB
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.
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Chuck Grimsby |
last post by:
(Repost, due to lack of submissions...)
The Microsoft Access Product Group (the people who build Microsoft
Access) want your help!
One of the main things we're working on for the near future...
|
by: Rob Geraghty |
last post by:
I've just spent some time looking through FAQ sites and searching the
google archives of this newsgroup, but I still haven't been able to
find a clear explanation of an issue with multi-user...
|
by: Null Reference |
last post by:
Anybody here who can explain or point me to a link ?
I wish to create a blank MS Access DB file programmatically
using C# .
Thanks, nfs
|
by: Can |
last post by:
I have a Microsoft Access front end, SQL server backend and I am using .dll
files to complete the n-tier application. How do I reference an unbound
textbox (from Microsoft Access) in the .dll?
...
|
by: TC |
last post by:
I need to design a system which represents multiple "projects" in SQL
Server. Each project has the same data model, but is independent of all
others. My inclination is to use one database to store...
|
by: fernan |
last post by:
Hi I don't know how to build a query which access to multiple databases.
Example:
On the same server I have DB1 and DB2
I want to SELECT TABLE FROM DB1 INTO CURSOR AND THEN I WANT TO MODIFY A...
|
by: JamesSykes |
last post by:
Hi,
I am relatively new to Access and VBA. I am trying to import a number of database files (just really want the contents of their tables) from a specified folder into a master table that...
|
by: richard.crosh |
last post by:
What is the IBM recommendation for the number of DB2-LUW databases per
instance on AIX? With Oracle, it is one-to-one. In DB2 multiple
databases can co-exist in an instance but is this...
|
by: JoyceBabu |
last post by:
Can anyone plz tell me the advantages and disadvantages of using multiple databases / single database for all tables.
I have a site with more than a 100 tables. Current all the tables are in a...
|
by: cj2 |
last post by:
If from withing one program I want to access multiple databases on a sql
server do I need to make multiple connections? The connection string
says initial catalog. I'd assume I could denote the...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |