Microsoft Most Valued Professional MVP - Access Expert

mvp.png

  Boyd Trimmell, aka HiTech Coach
  Microsoft MVP - Access Expert


What is a Microsoft  Most Valuable Professional (MVP)?
Microsoft  awards the Most Valuable Professional (MVP) in recognition of significant contributions made to technical communities by sharing high quality, real world expertise. Microsoft Most Valuable Professionals (MVPs) are a worldwide network of exceptional technical community leaders.

"If technology doesn't work for people, then it doesn't work."
- Kim Vicente

Information about splitting your database into an Application ( Front End ) and Data ( Back End )

Deploying Application (Front End) Updates

There several methods you can use to deploy the new Application (front End) version to all the users on the local network (LAN).

Here are some of the commonly used solutions:

1) Manually copy the file from the server to a the local folder.

2) Use a .CMD fiule to copy the new version from the server and launch the application (front End)

3) Use some type of auto updater. This will automatically detect and copy a new version application (front End).

Tools to help deploy your Access Application (front end) on a LAN

Auto FE Updater

Application Starter

BTAB Development - Free access Code for deploying updates

Easy Front-End AutoUpdater for large (or small) database environ

Front End Automatic Update to New Version

Using  VBScript

Peter Hibbs' VB6 app  Front End Updater Utility

How to Manage Your Linked Tables

Once you have learn how important it is to Split your Access database into application and data you will need become very good at managing your linked tables.  This is important if you want to be able to switch to a different back end. I am regularly switching between live data (back end) and test data (back end). You will also need to manage your linked tables if you move your current back end to a new location. When you move your back end or want to switch to a different back end you must tell Access when to find the back end you want to use. Access does not automatically locate the back end for you.

When you create a linked table Access stores the full path the back end database for each table. When you need to switch to a different back end location you must do one of the following:

 

1)      Delete all the linked tables. Recreate the linked tables.

2)      Use the Linked Table Manager to change the current linked tables to a new location.

3)      Use VBA code to relink the tables. You could automatically relink the table to a known location. You could also

provide a File browser dialog to allow the user to locate the backend.

 

Relinking tables in Runtime Mode

One of the issues with using the Access Runtime is how to provide an option for a user to select the location of the back end.  I normally use my own custom Link Table Manager using VBA code to handle the relinking.

 

VBA Code for relinking your tables:

J Street Access Relinker

Boiler Plate Database - By the Utter Angel -  includes table reattachment

ReLinker: multi-BE, Label Table Description to show Connect info  -  UtterAccess.com

Relink / Swap Between LIVE, TEST and LOCAL Data  - UtterAccess.com

Reconnect Attached tables on Start-up - by Peter Vukovic

Relink Access tables from code - by Dev Ashish

 

Additional Articles and Resources:

Get Back End database Location  - VBA code

Microsoft Jet Utilities

Reconnect Attached tables on Start-up

Splitting your Access database into application and data

By splitting your Access database you separate the application into the front end and data (tables) into the back end. This is a good practice for all software including Access applications. This is why I recommend that ever database should be split into an application/front end and data/back end . It is not just for multi-user applications/databases. I always split a database from the start. Even  if it is just for a single user on a standalone computer.

Splitting a database is not just for multi-user databases.

It's about a good software design!


Why you should split every  database used by multiple users at the same time

An Access database should never be opened by more than one user at a time. What? Access is supposed to be able to create a multi user database. I can be used for multi user applications. The way you do this is to split the database into app (front end) and data (backend). Each user will have copy of the app (front end) that is not shared. The data (back end) is shared by having each user’s app (front end) link to the tables is the shared back end.  Only the app (front end) is actually opened by Access directly.

Good Multi User software is split into  Application and Data

I don’t know of any good multi user software that is nt split into a front end and back end.  All software that use a RDBMS like an SQL Server, is split. The RDBMS is the data or back end.  This Includes web sites A web site is a back end. The browser on each user’s PC/device/smartphone is the front end. . The front end is the ASP/PHP/web scripts that generate the HTML to be sent to the end user's PC/device.

When to split your database?

While you might think it is a good idea to wait until you are ready to deploy your database to split it, I urge against this. I actually find that having a split database during the development and testing phases also aids in many ways.

Some reasons why I start every database split:

  • Most importantly, it helps protects the data!
  • Easily switch between back ends for better testing!
  • Designing in the same environment that the application will be used allows better testing as you build the app.
  • easy to test ideas on copies
  • backups are easier
  • you avoid the need for retesting everything later after the app is split
  • Deploying updates to Forms, Reports, Queries, and Modules is as easy are replacing the Application (Front end) file.
  • If every user has their own copy of the front end then you avoid issues with different versions of Access being used at the same time.
  • You can exceed the 2 GIG database file size limit by splitting your data into multiple back ends.

With a split database your app (front end) can be 2 GIG and your data (back end) can be 2 GIG with a single back end.  This allows you to safely exceed the Access 2 gig limit for a single database.If you have some very large tables you could split  your data into multiple back ends. The downside is that you can't have the database engine enforce Referential Integrity between tables in separate databases.  By splitting your data into multiple back ends, your data can safely exceed the 2 gig limit for a single Access database. When your data grows to this size your really should consider upsizing your data into an SQL Server.


How it helps to protect your data

Having your application and data split adds another layer of protection for your data. How is this?

Several ways. Here are a few:

1) When you forget to make a back up and you run the compact and repair and it corrupts your front end database. You will not lose your data in the back end.

2) You can develop and properly test by using a copy of your data (back end). When you are ready to deploy you  relink to the production/live data.

I am sure you would never do this:
While testing you run an update or delete query and the WHERE condition was missing or incorrect. Oops ... now you just deleted all your data or updated all the records when it should have been just a few. Not a problem if you are using test data ( back end).

Protecting your Application (Front End)

When you are ready to deploy your database, you can help protect your application by compiling the application ( front end) into a MDE/ACCDE. This will prevent anyone from entering design mode for Forms, Reports, and Modules. You want to be sure you keep the original MDB/ACCDB since this is your source. If you want o make any changes you must use the original MDB/ACCDB. When your changes are completed, you will create a new replacement MDE/ACCDE.

Locking Down the Front End

I do not normally lock down the front end until I have made the ACCDE/MDE.  If running in with the Access Runtime version of the Full version in Runtime mode it will automatically be locked down. I still go through steps to lock down the front end for when opened with the Full version of Access.

Another reason to use an MDE/ACCDB

With Access in runtime mode (full version or the runtime version) running an MDB/ACCDB you must make sure that you do not have any  un-handled/ un-trapped errors. Any un-trapped errors in the runtime mode this will cause Access to shutdown (appear to crash) with an MDB or ACCDB. This is not true with a MDE/ACCDB. Also variables are not reset, etc. It is a lot more stable when errors occur. if you use an MDE/ACCDE.


How to split your database

I normally start event database split. I first create a database with only the tables. When I am ready to start creating the UI I create a new database for the front end. In the front end database I create linked tables to the back end database.

Splitting an existing database

You have several options to split an existing database:

Use the built in Wizard

Use the built in database splitter. This is the easiest and simplest method.

1)      Make a backup o0f the database.

2)      Make another backup of the database. Just to be safe

3)      Run the database splitter

Manually

You can manually split the database. The way I prefer to  use these steps:

4)      Make a backup of the database.

5)      Make another backup of the database. Just to be safe

6)       Create a new blank back end database.

7)      Import all the tables form the existing database un-split database.

8)      Open the original database and delete all the tables that were imported into the  new back end

9)      Create linked tables to the new back end

also see: How to manually split a Access database in Microsoft Access

Managing your linked Tables

One of the many advantages of having your database split is that it allows you to switch between different back ends.  I use a separate back end for development and testing. This allows me to do fully test without worrying about corrupting the live/production data.

One issue with linked table that you have to deal with is the fact that Access stores the full path the back end database for each table. This means when you move the back end to a new location you must do one of the following:

1)      Delete all the linked tables. Recreate the linked tables.

2)      Use the Linked Table Manager to change the current linked tables to a new location.

3)      Use VBA code to relink the tables. You could automatically relink the table to a known location. You could also provide a File browser dialog to allow the user to locate the backend.

For a more details on using the Linked table Manger, using VBA code to refresh the links, relinking with the Access Runtime , and VBA code examples cehceck out:

How to Manage Your Linked Tables

How to deploy your split Application

Back end Location

The back end or data will need to be in a folder where user have read and write permissions. I find it best for performance to keep the folder level to a minimum.  I live to not use any sub folder below the share name.

Example:

\\server_name\share_name\data.mdb

Front End Location

The Application or Front End is best placed on the local hard drive of each workstation. I like to place Application in the user’s profile folder. This does mean each user will have a copy of the Application. This is true even with a SQL Server back end or on a Terminal Server/Citrix.   I use the %appdata% environment variable to find the location. This works great with XP, Vista, Windows 7, and Terminal Servers.

Placing the Application (front end) in each User's Profile on the local hard drive has several advantages:

  • Insured that the Application (Front end) will not be shared
  • Reduces network traffic
  • Increased performance
  • By placing the Application in each user's profile will make migrating to Citrix or Terminal Services a lot easy.

See the links before for tools to help you deploy the Application (front end) to each workstation.

Problems if all users share one front-end file or non-split database


1. Poor performance. Every form and report object goes over the network when accessed generating extra network traffic.

2. Temp tables can’t be shared unless you are generating unique names for each user or making sure that only one user can do the same process at a time. Otherwise they can collide with one another.

3. Shared FE's (Front ends) have a tendency to corrupt with a lot of users in.  There are a multitude of factors that contribute to this.

4. If your making any design changes in objects on the fly, then the user needs exclusive access to the DB.

Multiple Versions of Access

When you have different  versions of Access installed on machines on the network it becomes very, very important that you split your database and every user has their own copy of the front end. You do not want different versions of Access opening the same front end or you will have reference issue. If Access 2007 opens the database it will upgrade the Office Library references to the 2007 versions.  If you later try to open the database with Access 2003 the references may not downgrade properly.

Where do I update forms, reports, queries, modules, and macros?

The Application (front end) is where all the forms, reports, queries, modules, and macros are located. Once you have made the all the desired changes and have them fully test, it is then time to deploy a copy to all the users.

When I am ready to deploy a new version, I first compile the Application (front end) into a ACCDE or an MDE for 2003 and earlier formats. If while testing, I relink to test data, I open the new ACCDE/MDE and relink the tables. I then go through the steps to lock it down. Once this is done, I copy the ACCDE/MDE to a shared location on the network.

Deploying Application (Front End) Updates

There are several methods you can use to deploy the new Application (front End) version to all the users. Here are some:

1) Manually copy the file from the server to a the local folder

2) Use a shortcut to copy the new version from the server

3) Use some type of auto updater. This would automatically detect and copy the new version.

Tools to help deploy your Access Application (front end) on a LAN

Auto FE Updater

Application Starter

BTAB Development - Free access Code for deploying updates 

Easy Front-End AutoUpdater for large (or small) database environ

Front End Automatic Update to New Version

Using  VBScript

Peter Hibbs' VB6 app  Front End Updater Utility

Where do I update the tables and relationships?

The Data (Back end) is where you will find all the Tables and relationships. This is where you will make all the table design changes and define relationships.

Note: You can view the relationships from the Application (Front End). You can also view the table design from the Application (Front End). This is just for your convenience. You MUST open the Back End (data) to make design changes.

I like to use test Data (back end). I will make the changes to the back end and test the new Application (front end). When the new Application (front end) is deployed, you must also deploy the changes to the Back End (Data). This is where it gets more difficult. Unlike the Front End where you can just replace the file, you must apply the changes to the production back end. To update the back end, first you need all the users out. You should have exclusive access to the database file. I usually use something to flag the front ends that the back end is off line and it disables the Application (front ends)

I highly recommend that you make a backup before applying your changes.

If you keep good documentation, you will have a record of the table changes you have made. You can manually apply the changes. Since I do a lot of remote updates to back ends, I use an updater application to make the changes to the back end (data)

Tools to help deploy back end updates

BTD Development - Free access Code for deploying updates

Peter Hibb's Back End Update Utility

Manage Remote Backend MS Access Database Programmatically With VB Code

DAO Programming Code Examples

Additional Resources

 For more information about splitting your database into a application / Front end and Data / Back End:, check out the following:

Significantly Improve the Performance of Microsoft Access Databases with Linked Tables

Splitting a access database, or how to run ms-access in a multi-user mode.

Splitting your Microsoft Access MDB into a front end and back end

Split your Access database into data and application

Utter Access Wiki article on Sharing your database

Have you split your database?


Get Back End database location

When  working with a split database, you might need to find the location of the backend.

The following code allows you to retreive the location of the back end from the linked table. You simple pass the name of a linked table and it will return  the back end's path.

Read more ...