Create and Verify Persistent Connection in Split DB

[AlexB123]

Hi all!

I have a database that has been split into multiple backends, and I am trying to troubleshoot and speed up a number of queries that are bogged down by joins to tables in each backend file. I have attempted to create a persistent connection using two methods:

1) I created small tables with three rows of test data for each back end file, and then created a hidden form and set the record source to the corresponding table. These forms are opened when the database loads.

frmConnex1 & tblConnex1
frmConnex2 & tblConnex2
frmConnex3 & tblConnex3

2) In the switchboard form of my database, I included the following code in the Form_Load event:

 Dim g_dbBE1 As DAO.Database
    Dim g_dbBE2 As DAO.Database
    Dim g_dbBE3 As DAO.Database
        
        Set g_dbBE1 = DBEngine.Workspaces(0).OpenDatabase("C:\Users\AlexB123\Test1_be.accdb", False, False, ";PWD=Collards")
        Set g_dbBE2 = DBEngine.Workspaces(0).OpenDatabase("C:\Users\AlexB123\Test2_be.accdb", False, False, ";PWD=Collards")
        Set g_dbBE3 = DBEngine.Workspaces(0).OpenDatabase("C:\Users\AlexB123\Test3_be.accdb", False, False, ";PWD=Collards")

I cannot confirm that either of these methods works. If I open my tblConnex1, tblConnex2, and tblConnex3, I see a lock file created under each backend files when I look at the containing directory. Also, I have other forms that create a lockfile when opened: they have specific tables as form recordsources and fields as control sources ... i.e., frmTesting has tblNames as a recordsource, WorkingStatus (a field in tblNames) as a Control Source, and a sql query as a Row Source for a combobox.

Neither of my methods creates the lock file. Does this mean the methods have failed, and are not creating a persistent connection? What can I do to test if these methods are working? How can I improve them?

Thanks

 

[xenou]

I would assume the non-existence of a lock file generally means there is nothing or no one currently connected. I have never used this "persisent connection" trick so can't speak directly to how to test it. What query are you having problems with? Any chance you can just fix your "slow" queries so they aren't slow - i'm not sure that the overhead of a connection is going to make a difference if slow queries are involved.