Five Reasons Why You Should Consider Splitting a Microsoft Access Database
By Ben S Beitler
If you have spent time considering, planning, designing and building an Access database
then you'll know the challenges developers face when deploying the finished database application.
In the development and design mode, your Access database runs perfectly! You are proud
of the time, sweat and effort getting each object to work well, look good and meet the
business workflows the database application was intended for.
But suddenly you deploy and implement the Access database into the "live" environment
only to your horror discover that end-users start to complain why it's slow, why it keeps
crashing or why the function failed to respond at all.
What changed from within the comfort of the developer's safe haven to the vulnerability
of the 'unsophisticated' user? - You can be sure end-users will find a way to break your system!
Of course, there many reasons why any of the above happens but performance is a very
important factor when dealing with multiple users working with your application.
When you have concurrent users accessing your database, you need to ideally split an
Access database and the following five reasons may help to explain why:
1. Performance can be improved keeping the Access database optimised as much as
possible. By having one Access database file holding only the Tables stored on a server
with another database file holding all the other objects (Queries, Forms, Reports and
Macros) stored on each workstation with a link to the server database, you start to split
"processor" processing time between client and server trying to keep network traffic to a
minimum and only call data when you really need to will help improve the performance.
2. Because each user will have their own dedicated 'front-end' system, you can provide
different front-end interfaces for certain users allowing access to a collection of selective objects
which is a way to control workflows and security.
3. On the other side, you have one data store (the server-side) which is automatically
backed up daily (by the normal server systems) and it maintains data integrity too. You
know therefore, should the end-user delete their own 'front-end' interface; not all is
lost - You simply give them another 'front-end' to use knowing the 'back-end' database was
never at risk.
4. Each user can change objects (if enabled) or more commonly, add their own queries
and reports to provide local based processing which will not disturb the main hub of the
database. Users have also been known to use the likes of Microsoft Excel to run "one-off"
types of reporting like Pivot Tables by connecting directly to the "back-end" database.
5. In larger organisations where it's not unusual to have several departments with
different versions of Microsoft Access (as far back as Access 97) that having a "back-end"
database on the server can be in essence an earlier engine format (i.e. Access 97 or
Access 2000) where the 'front-end' objects can be based in either Access XP, 2003, 2007 or
even 2010! Migration is kept to absolute minimum.
There are other benefits to splitting an Access database but I think the above will be
a good start.
How you split an Access database is simple enough - you'll find this in the Database
Utilities and Database Tools section (depending which version you are using).
In fact, locate the Database Splitter Wizard tool this will do all the hard work foryou!
Another tip for you! Before you split the Access database, it is always recommended
that your first Back-up the database and then carry out a Compact and Repair Database
action which are both available in the Utilities or Tools section (depending on which
version you are using).
I invite you to keep up to date with my articles and eBooks which covers a lot of
details and can be found at
Microsoft Access Database Tutorial.
| 
About BTH
User Agreement
Privacy
Site Map
Contact
News Feeds
RSS Feed
Follow @Stephen Bucaro
Active Server Pages
