Deploying
an Access Application over a Local Area Network (LAN).
For deploying over a WAN click here.
(If the server where the Access files are stored is not physically located in the same building as the client PC using it, then it is likely the environment is a WAN.)
To deploy updates over the Internet check out my Web Updater function.
I have tweaked my deployment methods for years and feel that the outline I have listed here works best (the majority of the time). Trust me, this solution will make supporting your custom Access application much easier.
- Split the database into a front-end/back-end application.
- Save the front-end application as a mde file (or ade for adp files).
- Share the database - create a folder on a network share. The following files should be placed in the folder.
- Application data file (back-end - .mdb)
- Application mde file (front-end - .mde)
- Batch file (used for startup - .bat)
- Use a batch file to deploy the front-end mde file on each users local PC.
Too often novice developers skip step 4. Skipping this step can only lead to frustration and headaches that many developers learn the hard way.
Check out the development and production environment I suggest to ensure that both the developer and users can work effectively.
Split the Database
Splitting the database is a must when deploying Microsoft Access applications.
MDE/ADE Files
An mde file offers many advantages and is excellent for deployment. Increased performance, source code protection, and securing objects are some of the benefits gained by using mde files. This file format locks down the application by disabling the user interface for creating/modifying forms, reports, and modules.
About mde files More Info ..
Creating mde files More Info .
In my own experience I have resolved reoccurring database corruption issues by using an mde in place of an mdb file. In one specific instance I was using a traditional mdb file in a mixed Access environment. Several users had Access 2002 installed on their workstations while others had Access 2000. At times my mdb file would decompile at runtime and certain functionality would not work properly. After several failed attempts to resolve the issue, I decided to save the mdb file as an mde format. This resolved the issue and ever since I have been committed to deploying mde files whenever possible.
Sharing the Database
Placing the database on a network share will allow multiple users to use the application. Here are some additional tips to consider that may help in supporting the application and increasing performance.
Share a database. More Info ..
Link tables by UNC rather than mapped drives. More Info ..
Put the application in the root of a network share. More Info ...
Batch File (for startup)
I find it more convenient to launch an Access database using a batch file instead of using a shortcut. So rather than creating a shortcut to an Access database on the users PC, I create a shortcut to the batch file.
Advantages of using a batch file
- The batch file requires no software other than Windows to run.
- It can deploy the mde front-end file locally.
- Local folders can be created if they do not exist.
- Easy to deploy updated front-end.
- Reduce user’s downtime (a major plus).
- Easy to create, implement and support (another major plus).
- Multiple command lines can be written to support multiple Access versions or installation locations.
- Easily add, change, and revise the logon process without having to go to each PC to install updated files and edit shortcuts.
- Easy new user setup – email them a shortcut to the batch file with instructions on how to save it to their desktop.
- Use windows commands to provide additional automation such as drive mapping, printer mapping, font installation, and more.
Below is a sample batch fie. Copy the script to notepad and place it on the server (as a .bat file). Then create a shortcut on the desktop to the batch file. The script can be secured by assigning Read/Execute NTFS permissions to the file. Edit the script to meet your needs (file locations, names, etc). The Pause line is for testing purposes; remove it when you put the script into production.
rem Check for local file - create directory and copy file if not present.
if not exist C:\MyDB\MyDatabase.mde mkdir C:\MyDB
Rem updates local copy with server file
xcopy "\\Server\Shared\MyDatabase.mde" "C:\MyDB\MyDatabase.mde" /y
Rem Launch application if in MSACCESS.EXE is in Microsoft Office XP folder
if exist "C:\Program Files\Microsoft Office XP\Office10\MSACCESS.EXE" start /max "MSAccess" "C:\Program Files\Microsoft Office XP\Office10\MSACCESS.EXE" "C:\MyDB\MyDatabase.mde" /wrkgrp "\\Server\Shared\MyDatabase.mdw"
Rem Launch application if in MSACCESS.EXE is in MS Office folder
if exist "C:\Program Files\MS Office\MSACCESS.EXE" start /max "MSAccess" "C:\Program Files\MS Office\MSACCESS.EXE" "C:\MyDB\MyDatabase.mde" /wrkgrp "\\Server\Shared\MyDatabase.mdw"
Pause
Deploy Front-end locally
Installing the front-end application on each user’s PC will increase the performance of the application. Each user works within their own copy of the application front-end which links back to a central back-end data file located on the server. Advantages of this method are:
- Increased Performance.
- Less chance of corruption in front-end.
- Developers can use temporary tables in front-end to quickly compile data.
- Updates can easily be delivered to user.
Novice developers quickly learn that they cannot make design changes in their Access applications while users are using it. A simple task, such as expanding a field on a report, requires the developer to have excusive access to the application. This requires the developer to notify and ensure all users close out of the application so the changes can be made. This tedious task is eliminated by locally deploying the front-end file. The developer can make changes to the master mdb file and then create a new mde file. The mde file is placed on the network share (since each user has their local copy, the one on the server can be overwritten because no one is in it) and using my batch file example, the new updated file is copied to the user’s local machine at next logon. So if the developer needs to make a quick change, once the mde file is created, all the user needs to do is log out then back in. Simple, fast, and effective!
Back-end changes would require users to be completely logged out of the application. Back-end design changes are best done at the beginning/end of the day, when users are out of the application, or should be scheduled.
Additional Tips
Here are some additional tips from FMS, Inc. that I use for managing deployment and increasing application performance.
Using the User Roster to Check for Users in a Database. View Tip
Verify Linked Tables in Your Application.View Tip
Increase Performance of Linked Databases. View Tip
Set table Subdatasheet Name Property to [None], or explicitly specify the name. View Tip
Also Check out
Tips from FMS, Inc.
Microsoft Access Performance FAQ
|
