Split Database
Deploying Application (Front End) Updates
- Details
- Category: Split Database Split Database
- Last Updated: 13 September 2020 13 September 2020
There are several methods you can use to deploy the new Application (front End) version to all the users on the local network (LAN).
Here are some of the commonly used solutions:
1) Manually copy the file from the server to a local folder.
2) Use a .CMD/.BAT file to copy the new version from the server and launch the application (front End)
3) Use some type of auto-updater. This will automatically detect and copy a new version application (Front End).
Tools to help deploy your Access Application (front end) on a LAN
BTAB Development - Free access Code for deploying updates
Easy Front-End AutoUpdater for large (or small) database environ
How to Manage Your Linked Tables
- Details
- Category: Split Database Split Database
- Last Updated: 13 September 2020 13 September 2020
Once you have learned how important it is to Split your Access database into application and data you will need to become very good at managing your linked tables. This is important if you want to be able to switch to a different back end. I am regularly switching between live data (back end) and test data (back end). You will also need to manage your linked tables if you move your current back end to a new location. When you move your back end or want to switch to a different back end you must tell Access when to find the back end you want to use. Access does not automatically locate the back end for you.
When you create a linked table Access stores the full pathname to the back end database for each table. When you need to switch to a different back end location you must do one of the following:
1) Delete all the linked tables. Recreate the linked tables.
2) Use the Linked Table Manager to change the currently linked tables to a new location.
3) Use VBA code to relink the tables. You could automatically relink the table to a known location. You could also
provide a File browser dialog to allow the user to locate the backend.
Relinking tables in Runtime Mode
One of the issues with using the Access Runtime is how to provide an option for a user to select the location of the back end. I normally use my own custom Link Table Manager using VBA code to handle the relinking.
VBA Code for relinking your tables:
Boiler Plate Database - By the Utter Angel - includes table reattachment
ReLinker: multi-BE, Label Table Description to show Connect info - UtterAccess.com
Relink / Swap Between LIVE, TEST and LOCAL Data - UtterAccess.com
Reconnect Attached tables on Start-up - by Peter Vukovic
Relink Access tables from code - by Dev Ashish
Additional Articles and Resources:
Get Back End database Location - VBA code
Reconnect Attached tables on Start-up
Splitting your Access database into application and data
- Details
- Category: Split Database Split Database
- Last Updated: 07 July 2021 07 July 2021
By splitting your Access database you separate the application into the front end and data (tables) into the back end. This is a good practice for all software including Access applications. This is why I recommend that every database should be split into an application/front end and data/back end. It is not just for multi-user applications/databases. I always split a database from the start. Even if it is just for a single user on a standalone computer.
Splitting a database is not just for multi-user databases.
It's about good software design!
Why you should split every database used by multiple users at the same time
An Access database should never be opened by more than one user at a time. What? Access is supposed to be able to create a multi-user database. I can be used for multi-user applications. The way you do this is to split the database into an app (front end) and data (backend). Each user will have a copy of the app (front end) that is not shared. The data (back end) is shared by having each user’s app (front end) link to the tables is the shared back end. Only the app (front end) is actually opened by Access directly.
Good Multi-User software is split into Application and Data
I don’t know of any good multi-user software that is not split into a front end and back end. All software that uses an RDBMS like an SQL Server, is split. The RDBMS is the data or back end. This Includes web sites A web site is a back end. The browser on each user’s PC/device/smartphone is the front end. The front end is the ASP/PHP/web scripts that generate the HTML to be sent to the end user's PC/device.
When to split your database?
While you might think it is a good idea to wait until you are ready to deploy your database to split it, I urge against this. I actually find that having a split database during the development and testing phases also aids in many ways.
Some reasons why I start every database split:
- Most importantly, it helps protects the data!
- Easily switch between back ends for better testing!
- Designing in the same environment that the application will be used allows better testing as you build the app.
- easy to test ideas on copies
- backups are easier
- you avoid the need for retesting everything later after the app is split
- Deploying updates to Forms, Reports, Queries, and Modules is as easy done by replacing the Application (Front end) file.
- If every user has their own copy of the front end then you avoid issues with different versions of Access being used at the same time.
- You can exceed the 2 GIG database file size limit by splitting your data into multiple back ends.
With a split database, your app (front end) can be 2 GIG and your data (back end) can be 2 GIG with a single back end. This allows you to safely exceed the Access 2 gig limit for a single database.If you have some very large tables you could split your data into multiple back ends. The downside is that you can't have the database engine enforce Referential Integrity between tables in separate databases. By splitting your data into multiple back ends, your data can safely exceed the 2 gig limit for a single Access database. When your data grows to this size you really should consider upsizing your data into an SQL Server.
Get Back End database location
- Details
- Category: Split Database Split Database
- Last Updated: 13 September 2020 13 September 2020
When working with a split database, you might need to find the location of the backend.
The following code allows you to retrieve the location of the back end from the linked table. You simply pass the name of a linked table and it will return the back end's path.