Create and Verify Persistent Connection in Split DB

AlexB123

Board Regular
Joined
Dec 19, 2014
Messages
203
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:

Code:
 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
 
Last edited:

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,824
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,606
Messages
5,625,767
Members
416,136
Latest member
senthil_sk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top