In the development and design mode, your Access database runs perfectly. But when you deploy the database, end-users complain that it's slow or it fails to respond. When you have concurrent users accessing your database, you may need to split an Access database and the following five reasons explain why.
Welcome to Bucaro TecHelp!

Bucaro TecHelp
HTTPS Encryption not required because no account numbers or
personal information is ever requested or accepted by this site

About Bucaro TecHelp About BTH User Agreement User Agreement Privacy Policy Privacy Site Map Site Map Contact Bucaro TecHelp Contact RSS News Feeds News Feeds

Five Reasons Why You Should Consider Splitting a Microsoft Access Database

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.

RSS Feed RSS Feed

Follow Stephen Bucaro Follow @Stephen Bucaro

Fire HD
[Site User Agreement] [Privacy Policy] [Site map] [Search This Site] [Contact Form]
Copyright©2001-2024 Bucaro TecHelp 13771 N Fountain Hills Blvd Suite 114-248 Fountain Hills, AZ 85268