Persistent Connection explained

You just went grocery shopping and your car is full of groceries. Which is faster?

Take bag from car, open door to house, put bag inside house, go back out door closing and locking it behind you, repeat as many times till done

OR

And, here’s the persistent connection part…
Take bag from car, open door to house, put bag inside the house and leave it open, repeat as many times till done

Note how much faster the second part is, no reopening and unlocking the door to get in. It’s the same with a database, you are holding the *connection* open for *quicker* requests for information.

Tom Wickerath Microsoft MVP (Access) added… Without a persistent connection, lock the door after each trip, and reset the security alarm system. Then, when returning with the second bag of groceries, disable the security system and unlock the door. Repeat this laborious sequence until you have all your groceries brought in.

Technically explained by Tom Wickerath Microsoft MVP (Access)…

A persistent connection is *very helpful* in speeding up shared Access applications, including relinking tables. You can run a simple experiment, timing how long it takes to relink tables with and without a persistent connection, and you should see a vast improvement with a persistent connection present. Persistent connections prevent the file server from continually deleting and re-creating a locking database file, when there is a single user using the application.  The FE clients can re-use a connection that has been persisted, instead of having to spend the time creating a new connection each time.  Regardless of your specific issue, I highly recommend that you implement persistent connections to all BE JET databases, for your Access applications.

So how do you maintain a persistent connection?  In your Utilities Module add…

Public rsAlwaysOpen As Recordset

If you don’t have a Table that is open ALL the time create one.  It doesn’t need any records it just needs to be a Tables linked in the Front end.  Then on a your Main Form or a hidden Form in the On_Open event procedures add…

Set rsAlwaysOpen = CurrentDb.OpenRecordset("YourTableNameHere")

Now, you want to make sure you close that connection when exiting your database.  So, on the On_Close event of the Main Form add…

rsAlwaysOpen.Close
Set rsAlwaysOpen = Nothing

Now you will have a persistent connection which will improves your use of the database not having to reopen it every time you open a Form.