Rx_
Nothing In Moderation
- Local time
- Today, 08:10
- Joined
- Oct 22, 2009
- Messages
- 2,803
This question comes up on how to distribute a Front-End Access copy that links to a Back-End Database copy a.k.a Split Access Database.
This is a call for best practices or examples including best website links to explain the process of deploying a split database.
I will be happy to take suggestions to update this section with improvements.
Discussion Scope
Cover the basic concepts of a Split Database and present the Scripting (BAT files) for distribution.
Best Practice
The best practice is to Split a Database. Create a new Access DB and move all the tables to the new Access DB a.k.a Back-End. On the copy with the Forms, Modules, Queries, Reports and macros (a.k.a. Front-End) create Linked Tables to the Back-End.
The Front-End should reside on each Users desktop. The Back-End should reside in a networked protected file folder.
Background:
Access Developers may start an application that includes Forms, Reports, Queries and Tables in a single MS Access Database file. The tables can be either local (MSAccess format) or Linked Tables to servers such as AZURE, SQL Server, Oracle, IBM, or others).
Multiple Users:
To enable multiple users, copy the Front-End to a Users Workstation each time the user runs the application. The Front end has Linked Tables to the Back-End Access DB with the tables. For Server based databaes, the Linked Tables connect to Server databases.
This guarentees that each individual user has thier own copy of the front-end during the database session. There are known issues for multiple users opening up the same copy of an Access Front-End at the same time.
Bat File Scripts:
The Bat file or a shortcut to Bat file is placed on each users desktop. The user should be instructed to use this shortcut rather than starting the applicaiton from the MS Access application itself.
See examples of the Bat file script in the replies.
Quality Control:
It is important that each user has the exact version and Service Pack (e.gl SP1) of Access running on the desktop that the Access Developer used for creating the Access Application.
Example: For Access 2013 without SP1 - the VBA modules may randomlly crash.
Version Control:
The xcopy on the network allows the Access Developer to replace one Access Front-End file. Each user will receive this copy on the desktop the next time they run the Bat file. This allows for a one-location-one-distribution.
A system of retiring the existing copy by renaming it.
Example: MyCurrentCopy ==> Retired 2014-10-31 MyCurrentCopy
Then replacing it with the new current copy, and rename it to MyCurrentCopy
This comes in handy should an error be found after the new release comes out. The Retired copy can be replaced quicky to allow the developer to "exit gracefully".
Running MS Access Front-End from a Network Folder
A variation of this bat file copies the latest production copy to a folder for each user. The user can then run MS Access against thier own personal copy of the Front-End that resides on a Network Folder for each user.
This will run. However, for applications with larger database sizes, for larger datasets that use queries, or for filtering large datasets with validation code; this may not run as efficiently. It could also add considerable network traffic. In short, I don't recommend this. Does anyone else have suggestions about this?
This is a call for best practices or examples including best website links to explain the process of deploying a split database.
I will be happy to take suggestions to update this section with improvements.
Discussion Scope
Cover the basic concepts of a Split Database and present the Scripting (BAT files) for distribution.
Best Practice
The best practice is to Split a Database. Create a new Access DB and move all the tables to the new Access DB a.k.a Back-End. On the copy with the Forms, Modules, Queries, Reports and macros (a.k.a. Front-End) create Linked Tables to the Back-End.
The Front-End should reside on each Users desktop. The Back-End should reside in a networked protected file folder.
Background:
Access Developers may start an application that includes Forms, Reports, Queries and Tables in a single MS Access Database file. The tables can be either local (MSAccess format) or Linked Tables to servers such as AZURE, SQL Server, Oracle, IBM, or others).
Multiple Users:
To enable multiple users, copy the Front-End to a Users Workstation each time the user runs the application. The Front end has Linked Tables to the Back-End Access DB with the tables. For Server based databaes, the Linked Tables connect to Server databases.
This guarentees that each individual user has thier own copy of the front-end during the database session. There are known issues for multiple users opening up the same copy of an Access Front-End at the same time.
Bat File Scripts:
The Bat file or a shortcut to Bat file is placed on each users desktop. The user should be instructed to use this shortcut rather than starting the applicaiton from the MS Access application itself.
See examples of the Bat file script in the replies.
Quality Control:
It is important that each user has the exact version and Service Pack (e.gl SP1) of Access running on the desktop that the Access Developer used for creating the Access Application.
Example: For Access 2013 without SP1 - the VBA modules may randomlly crash.
Version Control:
The xcopy on the network allows the Access Developer to replace one Access Front-End file. Each user will receive this copy on the desktop the next time they run the Bat file. This allows for a one-location-one-distribution.
A system of retiring the existing copy by renaming it.
Example: MyCurrentCopy ==> Retired 2014-10-31 MyCurrentCopy
Then replacing it with the new current copy, and rename it to MyCurrentCopy
This comes in handy should an error be found after the new release comes out. The Retired copy can be replaced quicky to allow the developer to "exit gracefully".
Running MS Access Front-End from a Network Folder
A variation of this bat file copies the latest production copy to a folder for each user. The user can then run MS Access against thier own personal copy of the Front-End that resides on a Network Folder for each user.
This will run. However, for applications with larger database sizes, for larger datasets that use queries, or for filtering large datasets with validation code; this may not run as efficiently. It could also add considerable network traffic. In short, I don't recommend this. Does anyone else have suggestions about this?
Last edited: