Making your MS Access database shareable and maintenance friendly.

 

Many programmers have learned the hard way about maintainability of database applications in a shared environment. Here is a strategy that will allow your application to be highly maintainable and perform well in your network environment.

 

Applications that perform well in a single user desktop environment may become unusable or difficult to maintain in the shared environment of an office network. The first step in maintainability of an application is to split the database into a “front end” and “back end” file. The back end contains your data files, the access tables. The front end contains the application, a user interface, the customers window into your data. By separating the user interface from the data, the application can be changed without affecting the data.

 

MS access provides a utility to do this. In access 2000, go to Tools, Database Utilities, Database Splitter. Make sure you create a backup file before doing this. To do this manually, create a new blank database, import the tables from your current application. Then open your current application delete the tables from your application and then link the tables from your new database with the imported files. Again, ensure you back up your files before you start this change.

 

The next step is to locate a directory on your network where you can share the application. I suggest that the files (back end) and your application (front end) should be in separate directories. This provides less confusion for your users and does somewhat protect the files from prying or curious eyes.

 

Once you have located your application front end and back end files, you will need to map the tables to their new location. Open the front end application and open the database window (by pressing F11). Goto Tools, database tables, Linked table manager and select all your tables. Remap them to the network location.

 

You could now provide a shortcut for each of your users to the front end application and let them run the application from there. The downfall of this methodology is that everyone must be out of the application if you want to make any changes to the menus, reports or forms. A solution to this problem is to copy the application to a user specific area, and have the user run a copy of the application rather than the original. The risk here is having users on a different revision level of your application based on when they copied the application file.

 

I suggest that you create a small visual basic script that copies the application to a user specific area and runs the application automatically. Giving each user a shortcut to this visual basic script to start your application ensures they get the most recent version of your application when they try to run it. This approach also means that no-one is running the original application front end, so you are free to make modifications at will (rather than late at night or early in the morning when no-one is running the application). The code for this script is outlined below. You will need to modify the file names and locations for your script to work.

 

 

A Visual Basic script is simply a text file saved with a .vbs extension so that windows knows to run this script.

 

' Start of VBScript

' Move the files to a local directory and run from there

' This allows updates to the software to be easily distributed

' Create the directory if it does not exist

 

Option Explicit

Dim objFSO, objFolder, objShell, strDirectory, objFileCopy, objGuyFile

strDirectory = "u:\ptr"

' set this path to the directory for the user files

 

 

Dim strFilePath, strDestination

Dim strFileText, strFileText2, strFileText3

 

' Create the File System Object

Set objFSO = CreateObject("Scripting.FileSystemObject")

 

' Note If..Exists. Then, Else ... End If construction

If objFSO.FolderExists(strDirectory) Then

   Set objFolder = objFSO.GetFolder(strDirectory)

'   WScript.Echo strDirectory & " already created "

Else

   Set objFolder = objFSO.CreateFolder(strDirectory)

' WScript.Echo "Just created " & strDirectory

End If

 

' Copy the file

 

strFilePath = "\\Avsnw3\prod\OrderTrack\files\PTR.mdb"

strDestination ="u:\ptr\PTR.mdb"

 

Set objFileCopy = objFSO.GetFile(strFilePath)

' Copy the file to its destination

objFileCopy.Copy (strDestination)

 

 

' Copy the file

 

strFilePath = "\\Avsnw3\prod\OrderTrack\files\enablePTR\PTRaccess.mdb"

strDestination ="u:\ptr\PTRaccess.mdb"

 

Set objFileCopy = objFSO.GetFile(strFilePath)

' Copy the file to its destination

objFileCopy.Copy (strDestination)

 

 

 

' Run the file

 

Set objShell = CreateObject("WScript.Shell")

objShell.Run "msaccess.exe u:\ptr\PTR.mdb"

 

Wscript.Quit

 

' End of VBScript

 

This particular script copies two files. The 1st one is the application that points to a networked back end file. The 2nd is a database file that contains tables specific to the user currently using the application (current user information).

 

Hopefully this application gives you some insight into creative methods of customizing your MS Access applications and stimulates some innovative approaches to solving application challenges. If you find this article useful, send me a note and let me know how you utilized it in your application. Contact us for custom application development … happy coding!

 

Norm Bain

NBI internet

http://www.nbiinternet.ca

email