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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,694
Office Version
  1. 2013
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,109,425
Messages
5,528,688
Members
409,830
Latest member
KT50

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top