A common question raised by many Access users creating a multi-user
database is how best to deliver your solution so that it is secure,
fast, and upgradeable. Access database deployment within your
organization, or to your end customers, should be well thought out
in advance to avoid performance issues, reliability problems, or
potentially significant security or data loss issues. This
article discusses key best practices to deploy your Access database.
This item is a must to make upgrades to your end users easier.
A split database in MS Access simply means you will have one file
that contains your tables only. This is sometimes called a
"back end" or "data file." For simplicity, we'll refer to it
as your Access Data File. The second file is what is usually
called your "front end" database. There is really no data
stored in the file, it only contains your forms, queries, reports,
macros, VB modules, and links
to your Access Data File.
The advantages are:
- Store your data file anywhere
- With your data
separated from the user interface (forms/queries/reports) and
business logic (VB/macros), you can keep the data anywhere that
your users are able to gain access. Most commonly, on a
shared network drive or even a shared folder on your own machine
(as long as your machine is on when the file is needed by other
users!). You can
back up your Access Data File whenever you need to, and
relocate the file if needed. You will need to make sure
the front-end database file(s) always know where the Access Data
File resides. If it moves, you can manually restore the
links to the new location, do so programmatically, or use a tool
like UI Builder for Access to help
prompt the user to find the new location.
- Upgrade Your Front-End Databases without Data Loss
- Storing your forms/reports/macros/VB in the same file with your
tables also presents a problem when you need to make updates.
Particularly if you plan to deliver your database to users at a
different location where you don't have direct access to the
database file. Sending users a new version won't work, as
the data would be overwritten, or you would have to ask the
users to import their existing data tables. With a split
Access database configuration, you simply send them a new
version of your front-end file and their data remains intact.
- Avoid Multi-User Collisions
- If you store all your
objects in one file, including your tables, Access will usually
have difficulty when multiple users attempt to open the same
database file. While Access does have record-level
locking, you may still receive errors that another user is
currently in the database and you cannot make changes. To
avoid this, you provide each end user with their own front-end
database file, each linked to the same Access Data File.
Splitting your database is quite easy with the built-in Access tool. Be sure to make a copy of your database before you begin just to be safe. In Access 2000/2003, select "Tools>>Database Tools..." from the menu, then "Database Splitter..." In Access 2007/2010, select the Database Tools ribbon tab, then "Move
Data" and then "Access." The wizard will take you through the
process of selecting the tables to move, and what the your data file
will be named. It may seem trivial, but we recommend adding
"_datafile" to the end of the name so you are sure you know this is
the data file, not a front-end your users can delete.
preparing your front-end database file for delivery to users, you
will want to consider what you want to allow your users to be able
to do with your front-end file. If you want to allow them to
make design changes, open tables directly, edit or view VB source
code, then you can deliver the file "as is." More commonly,
you will expect users to have much more limited capability to simply
view the forms and reports as you have prepare them, without being
able to view or change the form/report/code design. The
easiest way to do so is to compile Access into an MDE or ACCDE file
format. A standard Access database file is in a
"semi-compiled" state which means Access does some level of
compilation of any VB code you have created on the fly.
Compiling your Access database simply means that a new file is
created with the extension ending in "e" instead of "b," and the
objects are fully compiled such that a user cannot open a form or
report's design view, nor can they inspect your VB code. This
protects your intellectual property, prevents many types of
tampering, and also makes your database load (only slightly) faster.
A few other points to help with front-end preparation:
- Navigation and Forms
- Make sure your database includes clear navigation, and we recommend you also use our
10 Tips for a More Dynamic Access Form
to add polish and make sure your users are more productive.
- Remove Unecessary Menus and Options
- In most cases, you'll want to hide the Access database window (2000/2003) or Access Navigation Pane (2007/2010) and provide your own Access menus.
To hide the database window in 2000 and 2003, select
"Tools>>Startup..." from the menu, and uncheck the "Display
Database Window" option. In Access 2007 and 2010, select
the Office icon, then the Access Options button. In the
dialog, select "Current Database" and uncheck the "Display
Navigation Pane" option. Lastly, all versions allow
you to disable Full Menus (the ribbon in Access 2007/2010) and
right-click Shortcut Menus if you want to further limit user's
Compiling your front-end into an ACCDE or MDE format helps
protect some of your intellectual property, but if you are planning
to store sensitive information in your Access project, we recommend
you implement the necessary security measures to be sure your data
is protected to the extent needed. In some cases, that may simply
be hiding the navigation pane to minimize the possibility that a
user can find your tables without Access know-how. In more extreme
cases, it may mean
password protecting and/or encrypting
front-end and data file.
- Access Workgroup Security
- Because the
Access 2007 file format does not support Workgroup Security, we
recommend staying away from this approach since it will limit your
ability to move to the 2007 format when it becomes advantageous or
even required by future versions of Access.
- Table Security
- Ultimately, a
determined user will likely be able to get to the data if they have
enough Access know-how. A linked table in your front-end is
technically a "trusted connection" where if the user can open the
database window or navigation pane, they will be able to view data
in the linked tables even if the data file is password protected.
If you hide the database window/navigation pane, disable full
menus, and also disable the "Access Special Keys" option, you
have made a large step to preventing a user from being able to
get to the tables.
- Navigation Security
- Even after you secure your database tables, you may decide
you want to limit what information some users view in your forms
and reports, and what forms and reports they can even launch.
The Enterprise Edition of UI Builder offers the ability to
create user-level menus in Access using the user's windows
login or a custom menu prompt, or you can create your own custom
menus and routines to log users in and present different options
to them. At a form level, you may want to consider whether
you will obscure certain information based on the user, such as
National ID or Tax ID.
Your Access Data file (back end) should be located on a network
folder where all users can open files in the directory. You
can also use this mechanism to limit who can access your database.
For example, you may set up your file server folder permissions to
only allow the 10 users you want to be able to open your database.
If you are delivering a solution to your client, you may not have
control over where the data file resides, but you can provide
guidance on setting up network folder security, and making sure the
folder is included in nightly backups.
When deciding where to
store the Data File, it is also important to consider the file
server age, hardware, and network latency. The last thing you
want is a client or user complaining that your application is
painfully slow. In some cases, it may just be a slow server.
We also recommend creating a persistent connection to a table in the
Data File for each front-end when it is open. Simply put,
Access will attempt to lock and unlock the Data File each time you
close a form and open another. By creating a persistent
connection, Access keeps the Data File open for the entire time the
front-end is open. To do this, you can either launch a hidden
form at startup that uses a table with a few records in the Data
File, or open a recordset to a table in the Data File, and keep it
open until the front-end is closed. If you are using UI
Builder, this is handled for you when UI Builder starts up. UI
Builder creates a connection to the
event log table
(tblEventLog) that remains open until UI Builder
If your solution will be used by a single end user,
and you store their Data File on their machine, keep in mind that it
will be easiest to have a consistent directory to store the Data
File on your machine (for development) and the end user's (for
production). If you both use the same path on your local PCs,
you won't need to remap the linked tables when your end user
installs your front end database file.
The front-end database files should be installed on each
individual's PC. It will load faster, and you minimize the
risk that two users open the same front-end on a shared network
folder (causing the Access multi-user error messages we covered
previously). Importantly, your end users will not need the
full version of Access unless you are allowing them to make design
changes to your forms/reports/code. You can save a great deal
in MS Access licenses by leveraging the
Access Runtime edition
. When you install the front-end,
you can use the free
Access 2007 developer extensions
from Microsoft to create an
installation file, or us a free installer like
(what we use and love). Make sure your main
front-end form or an easy-to-access form has the version clearly
labeled. That way if you need to troubleshoot, you or the user
can quickly tell what version they are running.
important to note that the Program Files directory in Windows Vista
and Windows 7 is no longer considered a "read/write" directory for
most purposes. Consequently, you'll want to install the
front-end file in one of the new and accepted places to installed
read/write programs, such as:
- The user's Documents folder
When the time comes to upgrade your front-end file with new changes,
all you need to do is release an update to the end users with the
front-end file only. They simply need to run your provided
installation file, or replace their current copy of the ACCDE/MDE
front end file you initially provided.
If you have direct access to the Data File, making changes to the
tables is usually easy. Your biggest task will to be to make
sure all users have exited their front-end databases so that you
have full read/write access to the table design.
If you are
delivering a packaged solution to customers, changing their Data
File can be a bit more involved. A few approaches to consider:
- Future Use Fields
- We use these quite a bit in our products. They aren't fool proof, but they can help you
minimize table changes when you want to add a few new features
that require more fields in an existing tables.
Essentially, you create a set of fields in each table named
"Future-Use-1" "Future-Use-2" and so on. You may want to
create 4-5 that are Text(255), several more that are Double
numeric fields, and a few Date fields. When you decide you
need a new text field to store something you didn't know your
customers/users wanted to track, you've got a field available to
do so. The form will have a caption like "Customer
Segment" while in the table (which your users won't see) you are
making use of "Future-Use-1." Avoid adding too many future
use fields, as it can (minimally) increase the space each row in
the table will consume on disk. But if you have 200,000
records, an extra 2kb per record can indeed make a difference!
- Upgrade Code
- Another option is to have a flag set in the front-end file to run upgrade code when you deliver a new version of your front-end file to users. When your database opens, if the flag equals true, your application attempts to open up the Data File (using DAO or ADO) and make the changes to the table(s).
After it is done, it sets the flag to false. This does
require solid experience with ADO or DAO, and we won't post any
code here. But it is entirely feasible to do.
Register for our forthcoming eBook - 'Using Microsoft Access For Greater Efficiency'
where you can get a comprehensive view of how Microsoft Access can
help you be more productive, when to choose Access as a solution,
best practices, and where to get help online.