Anyone that knows anything about developing a database knows that best practice dictates that you split your database into 2 components: a Back-End containing the tables (your data) and a Front-End containing everything else (queries, forms, reports, macros, modules, …).
What many developers seem to overlook however is the importance of always creating a persistent connection between the Front-End and the Back-end.
In a standard split database, each time you run a query, open a form, run a report, the Front-End must first establish a connection with the back-end, then when you close that object the connection is dropped/closed. Then you open another object, it must, yet again, establish a new connection, … You get the idea. Each time, establishing the connection takes time. How much time depends on numerous factors, your network, is the database secured/ encrypted, …
Think of it this way, imagine you have a multi-question survey to call people with. Is it better to dial a number, wait for someone to pick up at the other end, explain who you are, ask your first question and then hang up. Then repeat the same steps for your second question. And then yet again for your third question, … OR does it not make more sense to dial a number, wait for someone to pick up at the other end, explain who you are, ask your first question, ask your second question, ask your third question, …, and only hang up once your are through with your survey! The proper answer to this question is pretty clear cut if you ask me. Well, it is the same for your database!
To minimize this impact on your database and improve performance it is critical, IMHO, to always establish a persistent connection as soon as your front-end load. This way the database need not create a new connection each time you choose to do something, it already exists, so it can simply use it.
How to Create a Persistent Back-End Connection
Nothing could be simpler!
In the Back-End
- Create a new table (you can name it anything you’d like)
- Create a couple text or number fields of your choosing to the newly created table
- Add 2 or 3 simple records to the newly created table
In the Front-End
- Link the newly created table from your Back-end
- Create a new form based on this table
- Part of your AutoExec Macro’s code, add a line to automatically launch the form at the startup of your database in hidden mode.
So what have we done? Quite simply, since, as I stated previously, opening any bound object forces the Front-End to establish a connection with the Back-End, we are simply opening this form so it will establish a connection back to our table. We set it in hidden mode so the end-user isn’t ever even aware that it is there, and so they do not accidentally close it.
For complicated, multi-Back-End databases, a separate persistent connection should be established with each Back-End file.
Share and Enjoy