Create a development and deployment environment that does not inconvenient  the developer or the users.

A poorly planned environment can be frustrating for both the end users and the application developer.  Common problems include:

• Increased downtime due to instability and inefficiency.
• Having to track down all the users to ensure they are closed out of the application in order to troubleshoot an issue, deploy a new build, or make a change to the application.
• Inability to tell who is currently using the application.
• Inability to prevent users from opening the application while trying to make changes or perform maintenance.
• Accidental deletion or modification of production data.
• Unable to restore data or application because of an insufficient backup process and practices.

My Solution

My Microsoft Access application environment consist of the following

• Dev -   my development environment
  
• Test - my testing environment
  
• Prod - my production/runtime environment
  
• Backup – a storage area designated for backup files

This may sound like a lot of work, but it is quick and easy to set up and will save you from many of the pains and headaches listed above.  I will quickly list the environment structure, then explain each one.

Access Application Name: myApp

Dev
C:\Dev\myapp\myAppMaster.mdb
C:\Dev\myapp\myAppData.mdb
C:\Dev\myapp\Deploy\

Test
X:\myapp\Test\TestmyApp.mde
X:\myapp\Test\TestmyAppData.mdb
X:\myapp\Test\Deploy\

Prod
X:\myapp\tmyApp.mde
X:\myapp\myAppData.mdb
X:\myapp\myAppData.mdw
X:\myapp\myAppLogin.bat
X:\myapp\Deploy\

Backup
Y:\Backup\myApp\

Dev

Yes I keep my development files on my local PC.  Why?  Several reasons:

• Performance – my application and data will load faster locally then over the network. I don't have to wait long for forms and reports to open.
• Less Downtime – In 10 years, I have never worked at a place where my local desktop computer was ever down more than the network.  When the network goes down – I may lose internet, email, and other services, but I can still develop my Access applications.
• Less confusion – I know exactly where the development copy of my application is and the users can't get to it.
• Stability – whatever I am doing it is not effecting the users and vice versa – my development environment is separate from the environment they are working in.

Now let’s look at the files I am storing locally in my Dev environment (C:\Dev\myapp\myApp)

• myAppMaster.mdb – This is an mdb file, notice the addition of Master in the name of the file.  This clearly identified this file as the developer's copy of the application. This is my most critical file - without it, I'm out of a Job.
• myAppData.mdb - This is my local copy of the date.  This way I cannot accidentally modify or delete actual live date. (Yes, I've learned this the hard way.)
• Deploy – This is a folder of files I want deployed (installed) to the user’s local computer.

And as far as you question about what happens if my local PC crashes or hard drive malfunctions…I have a back up environment.

Test

Is simply that, a place to test my application changes just before I put it into production?  My test environment sits within my application’s production folder on the network.  This allows me to:

• Test the applications performance over the network.
  
• Enable other users to test the application.
  
• Allows the application to be tested in a multi user environment.

Prod

Thisis my production environment.  This is where the live application and database reside.  (Depending on the sensitivity of the inflammation I may move the data file to a hidden folder - \\server\share$\myAppData.mdb

OK – so let’s look at the files

• X:\myapp\tmyApp.mde – I prefer to deploy mde files.  Increased performance, source code protection, and securing objects are some of the benefits gained by using mde files.  About mde files More Info ..
  


• X:\myapp\myAppData.mdb – this is the actual data.  This is my Client’s most critical file - without it, they are out of business.  So do My advice, do not add a field, delete a table, run a compact and repair, delete temporary tables, or make any changes whatsoever until you have backed it up FIRST.


• X:\myapp\myAppData.mdw – this is the security file (If you utilize Access Security, then you will have one.)  For those who do use access security please take note, do NOT name the file system.mdw.  When you create a new access application, you don’t name it db1.mdb do you?  Then why would you not change the name of the workgroup file?  I worked at one client site that had over 20 system.mdw files on their network share.  Each file related to a different MS Access application.  How would anyone ever know what mdw file correctly corresponded to the mdb file?  This file is also an important file to backup.


• X:\myapp\myAppLogin.bat – this is the script I run to create a local copy of the front end application (X:\myapp\tmyApp.mde) and then launch launch the application.  Click here to read up on who to create this file.


• X:\myapp\Deploy\ - This folder contains files I want to deploy to the user’s local environment.  Examples include: images, Active-X controls, and documentation.  Depending on the situation, I may want to access and store files on the user’s local system rather than the network.  A common example is a letterhead logo or watermark. For better performance, I often choose to link to an image on the local drive rather then a network share or embed the image.  Any file I want installed on the local drive I simply place in the deploy folder.  I can simply add a line to my logon script to included this folder.

Batch File Line:
xcopy "\\Server\Shared\myapp\Deploy\" "C:\ MyDB\Deploy\" /y /d

Note:  The /d switch will copy all source files that are newer than the time of existing destination files.

Backup

Y:\Backup\myApp\

I believe it is the responsibility of the IT department to back up files stored on the network share.  However, it is the responsibility of the developer to ensure there is a working good back up before any design changes are made and maintenance is performed.  Before making changes to an application, I back it up to the designated storage area.  Also, if a record become corrupted or is mistakenly deleted, it is much easier (and many times faster) to restore the record by coping if from a back up file that I have in my backup location, then it is to ask the IT department to retrieve it from a back up tape.

It is important to rename the files in this folder with a name that does not confuse it with the live files.  I generally change the extension so someone does not inadvertently use it.

These are the files are critical to the application and need to be backed up.

• C:\Dev\myapp\myAppMaster.mdb
• X:\myapp\myAppData.mdb
• X:\myapp\myAppData.mdw (if used)

You should also inquire about off-site storage and disaster recovery procedures.  If the client does not have one that satisfies your expectations, make sure to keep your own copy of the application (CD, DVD, laptop).  I would suggest NOT keeping your own copy of the data file.  (Privacy laws, information security, identify theft).  That last thing you want is you name and face on the 6 o’clock news because someone stole your laptop that contained a database file with thousands of names, addresses, and social security numbers.