The Art of Database Development: Establishing a Persistent Connection
When it comes to database development, seasoned professionals understand that best practices dictate a clear separation between two essential components: the Back-End, where your data resides, and the Front-End, which encompasses all the interactive elements like queries, forms, reports, macros, and modules. However, one crucial aspect that many developers overlook is the significance of maintaining a persistent connection between the Front-End and Back-End.
The Cost of Disconnection
In a typical split database setup, every time you run a query, open a form, or generate a report, the Front-End must first establish a connection with the Back-End. Once you close that object, the connection is severed. If you then decide to open another object, the process starts all over again. This repetitive cycle of connecting and disconnecting can be time-consuming and inefficient, influenced by factors such as network speed and database security measures. To illustrate this point, consider a multi-question survey scenario. Would you prefer to call someone, introduce yourself, ask one question, hang up, and repeat this process for each subsequent question? Or would it make more sense to establish a connection once—asking all your questions in one go before hanging up? Clearly, the latter approach is far more efficient. The same logic applies to your database interactions!
Enhance Performance with Persistent Connections
To boost your database performance and minimize connection overhead, it’s vital to establish a persistent connection as soon as your Front-End loads. This way, you eliminate the need for repeated connection setups; instead, you can utilize an already established link for all your operations.
How to Create a Persistent Back-End Connection
Creating a persistent connection is straightforward! Here’s how to do it:
In the Back-End:
- Create a new table (feel free to name it anything you like).
- Add a couple of text or number fields to this new table.
- Insert 2 or 3 simple records into the table.
In the Front-End:
- Link the newly created table from your Back-End.
- Create a new form based on this table.
- In your AutoExec Macro’s code, add a line to automatically launch this form in hidden mode when your database starts up.
What Have We Accomplished?
By opening this form upon startup—albeit in hidden mode—we ensure that the Front-End establishes an immediate connection with the Back-End. This means that whenever users interact with bound objects in the Front-End, they benefit from an already active connection without even realizing it! For more complex databases with multiple Back-Ends, remember to establish separate persistent connections for each Back-End file.
By following these steps and principles, you can significantly enhance your database’s efficiency and user experience!
Important Note: This approach is specifically applicable if you’re using an Access back-end; if your data resides in other RDBMS systems like SQL Server, PostgreSQL, or MySQL, this technique is unnecessary.
Explore More Best Practices
For further insights into optimizing your Access database, check out my article on Microsoft Access Best Practices. This guide highlights essential best practices that can help you sidestep frequent issues, ensuring smoother operations and improved performance. Read more about it:
This works with a split .mdb,but what if I wish to connect an .mde? I am using the jstreet table lniking code with the macro.
You setup the persistent connection form/code in the master mdb, once it is converted into an mde, the form will work just as it should. So whether or not you are using an mdb, or an mde, the setup is the same.
Okay, I think I get it now. I was worried about using a split master .mdb,
Thanks alot.
Thankyou for this article, I hadn’t encountered this concept. I just learned that cmdOpenForm and cmdClose Form actually trigger several events, and I would appreciate if you could help me understand how these events fit in with the show/hide technique needed for persistent connection
If the forms are always open but hidden, how do you “fake-open” them?
is doCmd.OpenForm the right way, or is it over the top, because the form is actually already open and loaded, and simply needs to be set to visible, (and maybe active, current)
If you want to retain the permanent connection, do you “fake” closing them, by hiding them but not closing them?
The idea is that you create a form which is used for nothing else than creating a persistent connection and is linked to a table just for this purpose and nothing else (we don’t want to create locks on a commonly used table …). With the above in mind, then user should never need to see such a form and this is why we use the DoCmd.OpenForm to open the form at the startup of our database in Hidden mode (so it is there, just not visible) and it will close when the database is closed. This should all be hidden and automatic to the end-user and they should never need to interact in any way with the process or the form/table involved.
Thank you for your article.
Just for clarification, if our split db automatically opens a form that is sourced from a query of a few tables in our backend, this would also work establish a persistent correction – correct?
As in, the form need not be sourced from a table, a query would also work?
Thanks!
Correct, a query will also create a connection.
Thanks for this Daniel, I hope its not too late to comment. I had a similar problem with slow responses over quite a large population of networked PCs; I was recommended to establish a permanent front end / back end link using the following code:
Define a public object
Public objRS As Recordset
On opening the database execute the following function:
Public Function OpenTableInMemory()
Set objRS = CurrentDb.OpenRecordset(“TblXXX”)
End Function
Where TblXXX is a back end table
When closing the database execute the following function:
Public Function ReleaseTableFromMemory()
objRS.Close
End Function
This all seemed very simple and straight forward, almost too simple. Would you agree that this should keep the link open? Appreciate your thoughts.
Yes, in theory, opening through code establishes the same connection and as long as it remains opened, you have indeed created a persistent connection.
FMS Inc. has code to do this, see http://www.fmsinc.com/microsoftaccess/performance/linkeddatabase.html
I did try this approach, and sadly cannot remember the details at present, but it failed. By using the tried and tested form approach, I had no problems. As such, I’ve always just stuck for the form approach.
The code should work fine, it truly should, but do test it to be 100% sure.
Daniel,
Just come across this post when trying to help someone on a forum.?
The MS SwitchBoard form does exactly this, does it not?
After reading this post I was going to implement this is my databases, though small, they will grow, but then thought ‘I am already doing this via the Switchboard form?’
It depends on your switchboard’s setup.
If the table it’s Record Source is based off of is local, within the FE, then no, it does not create a persistent connection and thus does not help.
If on the other hand, the table it’s Record Source is based off of is a linked table, within the BE, then yes, it can be used to establish a persistent connection. The risk here is that a user could close the form, and thus close your persistent connection. This is why I privilege a hidden form which the end-users cannot impact and thus guarantee my connection is never closed.
This is a great thread. I have a split database. The FE DB opens a switchboard form called frm_Main when the DB is opened. The form is not associated with any tables or queries. The switchboard allows the users to choose a form they would like to open to make an entry. Once they click the specific form it opens and the frm_main remains open as well. Can I have the hidden form run which you have mentioned previously to create the persistent connection in addition to the switchboard when the DB opens? Can you provide the instructions on how to have the hidden form run? I am not familiar with VBA code or any commands to run it.
OR
Should I create a table in the BE DB and call it tbl_Main and associate it with the switchboard form in the FE DB called frm_Main?
Thank you in advance.
I personally privilege a separate hidden form that no one can mess with, accidentally close, …
As for how to accomplish this, as part of an AutoExec macro or your switchboard’s opening event, you simply use DoCmd.OpenForm to open the persistent connection form in hidden mode. If you look at the DoCmd.OpenForm method documentation you will see a 6th input argument for the Window Mode in which you would specify the value of acHidden.
Something along the lines of:
This solution worked great. Thank you.
Would a persistent connection to an MS Access back-end (that sits on a network drive) hamper speed/performance of a read/write front-end (MS Access) opened on over 50 desktops? Assuming front end code and queries are optimized there a general way to allow up to 50+ simultaneous users to read/write in such a setup without speed issues, ie as if the back-end were sitting on the users’ hard drive and they were the sole connection.
Quite to the contrary, persistent connection speeds up (quite notably in many cases) database front-end execution. The number of users has no impact on the aspect of database design.
Is there any advantage to splitting a database if you keep both the front end and back end on a network server? I created a database that is used by only 3 or 4 other people. I quite often make changes to some aspect of the database and would hate to have to send updated copies of the front end to everyone whenever I make a change. Would the speed be increased any by splitting the database if both ends are on the server?
Every multi-user Access database needs to be split and each user needs their own copy of the front-end to avoid corruption amongst other things.
There are other benefit however, mainly it becomes much easier to push front-end updates since you no longer have to take the system offline to do so.
You may like to read http://www.devhut.net/2017/04/09/setting-up-an-ms-access-database/ and checkout the link provided in that section of the article to learn how you can automate the update process so it isn’t so tedious for everyone involved.
I should also mention, that you really should install the front-end on each user’s computer rather than on the server. As it is, they need to download the file each time the use it, vs. just opening it. Depending on the network, this can have a significant performance benefit and it also reduced bandwidth depending on the usage of the database.
Hi. I have a question: i have an Access database in a client-server format, and for some process the database response is so slow, there are 10 concurrent users in the network, the question is the persistent connection works in a client-server environment?. Thnak you in advance for your comments.
Yes, most certainly. This is one of these things that should be applied to any split database.
I appreciate your article
Daniel –
Thanks for this thread. I don’t think I saw an example of an instance where there is need to keep several tables from one BE db open at he same time.
I saw “For complicated, multi-Back-End databases, a separate persistent connection should be established with each Back-End file.” But what about several tables withing a BE db? Should there be a hidden form for each table?
Thanks in advance for your help.
You need only one persistent connection be created per back-end regardless of the number of tables within a back-end. And you want to use a bogus table to create the connection. Never use a data table or you risk locking records.
Thanks again. I’ll give it a go.
May lead to more questions!
: >)
Daniel,
I had the same question and am happy that you answered it already. I also have multiple Backends, mostly three. So I had the idea to use an already existing table, the version-table, that is not yet linked to a form, because it is either changed manually or by code. This table exists in all Backends. Could I create a single form and open it several times in hidden mode for each of the version-tables? The third backend is chosen by the user out of a filesystem dialog, so I can’t open them simultanously. Would that work or could it cause any trouble?
Yes, it doesn’t necessarily need to be a new table, but it need to be a table that isn’t really used by the users so it doesn’t cause a lock conflict.
The fact that the user selects the 3rd database poses no real issue. When they make the selection, you have the necessary information to connect a 3rd form to the same table in that database, simply change the form’s recordsource dynamically.
I have a multi user split DB and am encountering a runtime error 3045 “File already in use” would this help resolve this error.
No, I doubt it would.
Good day Daniel
I appreciate your work and I am greteful that we have your precious help.
I have a splited DB on accdb no accdb and I am facing issues with often corruptions.
I do not have any relations between tables.
The corruption comes in a different fields and tables every time.
Do you believe that the follow code (for persistentency) can help ? :
Public Sub PercSub
dim TblXDB as dao.database
dim TblXRS as dao.recordset
set TblXDB=currentdb()
set TblXRS=TblXDB.OpenRecordset(“PersistTbl”)
with tblxrs
.movefirst
a=!fieldX
end with
tblxrs.close
tblxdb.close
set tblxdb=nothing
set tblxrs=nothing
End Sub
This routine do I have to call it every time before to save a record ?
Thanks in advance Daniel.
If you want to go the route of using VBA code to create your persistent connection, rather than a table/form setup, I recommend using FMS’s code which can be found at The DAO OpenDatabase Method. As with either approach, you simply run the code (or open the form) once at the startup of your database (an AutoExec macro works well for this) and that’s it! The persistent connection will remain open/active until which time you close your database. This is why it should be the first thing you do when you open your db.
That all said, if you are facing frequent corruption, are you aware of: Access – Bug – Database is in an Unrecognized Format
I am so glad I found this article! I have researched this problem and seen so many solutions that are extravagant and code-heavy and didn’t want to implement any of them because I really felt there should be a simpler way. This solution is so elegant and simple, it’s genius. Thank you so much! BTW, it works fabulously 🙂
Great Job in explaining. Thanks Russ
Is there a way to verify that the connection is working? I did a test on my database. With the database being split (and both files still on my local computer) it took an extra 200 seconds to complete the same process as if the database was not split.
When I put the backend on my network shared folder it added 4000 seconds.
Same code, Same Data, just three copies of the same database and same data.
Any suggestions?
If you can open a table, then you know the connection is working. Whenever using a split database, it is critical to setup a Persistent Connection, refer to: https://www.devhut.net/ms-access-persistent-connection-in-a-split-database/. Hopefully this will help remedy your speed issue.