Access linked Backend tables is slow to open for 1st table, then fast once record-locking file is created - can I keep this lock file open?

Question

I have been working successfully so far with a database that has a split backend. During development the front and backends have been in the same drive, but now the backend is on a network drive in preparation for multiple users.

My issue is that whenever I now open a linked table (or form that reads data from it) from the frontend, the first table is always slow to load. And if I then close that table and open another table, that's also slow to to load. But if I open any table and then open a second table, the second table and any others thereafter is fast.

It seems it may be that it's related to the locking file having to be created, and then closing when the table closes, meaning there's a delay when that file has to be created again for the next table.

Is there way to create the record-locking file on application launch so that it remains available throughout a user's session?

Thank you

Answer 1

Yes, this is a wide known problem and for years we suggest you adopt what is called a persistent connection.

If you are doing development, then often what one can do is simple click on a table (any linked table) to open it, now you can work, design forms, and not experience huge delays during the development process.

While the above fixes the delays during and when developing, the SAME concept and approach can be used for when you run your application.

Like every applcation, you no doubt have some startup code. This code can go in the first form that you launch on applcation startup.

So, you can create a standard code module. Say it is Module1. In that module, you can place this code:

  Option Compare Database
  Option Explicit
  
  Public rstPersist    As DAO.Recordset
  
  
  Sub MyOpenPersist()
  
      ' opens a back end table to force and keep
      ' open the connection for greatly improved
      ' speed
      
      Set rstPersist = CurrentDb.OpenRecordset("tblDefaults")
  
  End Sub

And then in the FIRST form you launch on startup, in the on-load even, you can include this code:

 Call MyOpenPersist

Now, this will open a table to a "global" rstPersit recordset.

Now, and from that point on-wards, since the locking file has been created (which takes a long time as you note), then the applcation, forms, and everything will now run without that delay.

It does not matter which table you choose to open - only requirement is that the table is a linked table, since you can (and might) have some local tables in the front end for things like settings or whatever. So, in this case, I open up a table called defaults (and it great to have that table open, since it is a 1 record table with default things like City and default area code for phone numbers etc.). So, I in effect kill two birds with one stone so to speak, since I need a table of defaults for many things in the applcation anyway.