First Published 19 Oct 2024

This article was prompted by a recent thread by mloucel at Access World Forums: Unsplit Database

The standard recommended practice is to split databases with all tables in the backend. This article explains some of the most important reasons for doing this.
It also gives several scenarios where you may want to 'unsplit' databases with one or all tables moved to the front-end and explains two easy ways to do so.



Splitting an Access database into a front-end and back-end offers several benefits:

•   Improved Performance: Only data is sent across the network, not the entire database objects, which speeds up operations.
•   Enhanced Security: The back-end, containing the data, can be secured on a server, reducing the risk of unauthorized access.
•   Better Reliability: If a front-end file gets corrupted, it doesn’t affect the back-end, minimizing data loss.
•   Easier Maintenance: Updates to the front-end (forms, queries, reports) can be made without affecting the data in the back-end.
•   Scalability: Multiple users can access the same back-end database simultaneously without performance issues.

This setup is particularly useful in multi-user environments, ensuring smoother and more secure database operations.
However, it is also recommended in single-user environments to reduce the risk of data loss caused by corruption during development work.

While splitting an Access database into front-end and back-end has many advantages, there are situations where it might not be the best approach:

•   Performance Issues: In some network environments, especially with slow or unreliable connections, splitting can lead to performance degradation.
      Forms and reports might load slower because data has to travel across the network.
•   Maintenance Overhead: Managing two separate files (front-end and back-end) can be more cumbersome, especially if frequent updates are needed.
•   Compatibility Concerns: If users are on different versions of Access, ensuring compatibility between the front-end and back-end can be challenging.
•   Security Risks: If not properly managed, splitting can expose the back-end to unauthorized access, especially if the network security is weak.

In general, it is better to deal with any issues resulting from splitting rather than use those as excuses for keeping your databases unsplit.

However, there are certain valid reasons why you might decide to 'unspilt' (AKA 'splat') one or more tables or merge the entire database. For example:
•   User Settings: Storing user-specific settings in the front-end (FE) can simplify customization and ensure that each user’s preferences are readily accessible without
     affecting the shared data in the back-end (BE). However, there is the risk of user settings being lost when the front-end is updated.
•   Single Database Uploads: When uploading databases to forums for analysis, keeping everything in a single file can be more convenient.
     It avoids the hassle of managing and explaining the split structure to others who might not be familiar with it

If you do need to move one or more linked tables back to the front-end, you can delete the linked table then re-import the table(s) as local tables.
However there is an easier approach. You can just convert the linked table(s) to local table(s) using built-in Access functionality.

Right click on a linked table you wish to restore to the front-end and click Convert to Local Table.


You can also do this with multiple tables at the same time.


The process is very quick. After completion, all the selected tables will be local tables with the same names. The original tables in the external database are not affected.


NOTE:
The conversion can also be done using VBA on a selected table using DoCmd.RunCommand acCmdConvertLinkedTableToLocal
For example, I use a subprocedure Link2Local to do this:

Public Sub Link2Local(ByVal sTable As String)

'================================
' Purpose      : Converts linked table to local table
' Author       : Colin Riddington (isladogs)
' DateTime     : 06/10/2018
'================================

    ' Select the linked table
    DoCmd.SelectObject acTable, sTable, True

    ' Convert the linked table to a local table
    DoCmd.RunCommand acCmdConvertLinkedTableToLocal

End Sub

Typical usage:

Link2Local "MyLinkedTableName"

Feedback

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 19 Oct 2024

Return to Access Blog Page

Return to Top