BAT file shortcut for distributing Front-End to Desktops for split Access DB (1 Viewer)

[Rx_]

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?

 

[Rx_]

Example of a Front-End Distribution Bat File
Note: your "C:\Program Files (x86)\Microsoft Office\OFFICE15\MSACCESS.EXE" might vary

@echo Please wait while the version of the database you requested is updated to your desktop.
@echo off
@echo %username%
if not EXIST "C:\MyApplication\Profiles\%username%" GoTo LocalFileFolderWasNotThere
xcopy /Q /Y "[URL="file://\\MySharePath\MyNetworkFolder\Admin\MyDivision\MyApplication\MyApplication_JohnGault.accdb"]\\MySharePath\MyNetworkFolder\Admin\MyDivision\MyApplication\MyApplication_JohnGault.accdb[/URL]" "C:\MyApplication\Profiles\%username%\*.*"
REM TYPE NUL | choice /c:delay /td,5 >NUL
Start /max "C:\Program Files (x86)\Microsoft Office\OFFICE15\MSACCESS.EXE" "[URL="file://\\MySharePath\MyNetworkFolder\Admin\MyDivision\MyApplication\Profiles\%username%\MyApplication_JohnGault.accdb"]\\MySharePath\MyNetworkFolder\Admin\MyDivision\MyApplication\Profiles\%username%\MyApplication_JohnGault.accdb[/URL]" 
exit
:LocalFileFolderWasNotThere
@echo Creating You A New Profile
MD "C:\MyApplication\Profiles\%username%"
xcopy /Q /Y "[URL="file://\\MySharePath\MyNetworkFolder\Admin\MyDivision\MyApplication\REgulatory_JohnGault.accdb"]\\MySharePath\MyNetworkFolder\Admin\MyDivision\MyApplication\REgulatory_JohnGault.accdb[/URL]" "C:\MyApplication\Profiles\%username%\*.*"
REM TYPE NUL | choice /c:delay /td,5 >NUL
Start /max "C:\Program Files (x86)\Microsoft Office\OFFICE15\MSACCESS.EXE" "C:\MyApplication\Profiles\%username%\REgulatory_JohnGault.accdb" 
exit

 

[MilaK]

Hello,

I'm trying to create a batch file that copies the front end to the desk top. Could someone please explain what the line below references? Does it reference the back end?

"\\MySharePath\MyNetworkFolder\Admin\MyDivision\MyApplication\Profiles\%username%\MyApplication_JohnGault.accdb"

in

REM TYPE NUL | choice /c:delay /td,5 >NUL
Start /max "C:\Program Files (x86)\Microsoft Office\OFFICE15\MSACCESS.EXE" "\\MySharePath\MyNetworkFolder\Admin\MyDivision\MyApplication\Profiles\%username%\MyApplication_JohnGault.accdb"
exit

I'm assuming that

\\MySharePath\MyNetworkFolder\Admin\MyDivision\MyApplication\MyApplication_JohnGault.accdb

is the front end?

Thanks

 

[calvinle]

The only issue with using a .bat is the user is able to know where the main file is saved. That's why I don't like using .bat, I rather integrate code within my front end to lookup in the backup for version, if there is a new version in the backend, it will copy the new version of the front end tha is stored in the server and replace to the current version of the user.

That way the user does know where the backend is stored.