Many people deploy Microsoft Access database applications and neglect to provide the system administration necessary to properly support and maintain them over time. This becomes critical as the data it contains grows and becomes mission critical. Often, when something goes wrong, IT "professionals" are brought in to discover basic system administration are not in place. Rather than blaming the people involved, the Microsoft Access technology is considered at fault. We can do better.

Here's a repsonse I recently provided related to this issue:

First, I hope you have a disaster recovery plan in place. You may want to read my paper for what we consider best practices:Creating a Backup and Disaster Recovery Plan for Microsoft Access Database Applications.

Second, Access/Jet databases need to be periodically compacted to minimize corruption and bloat, and for optimal performance. The back-end database with the data is what needs to be compacted. You can do that manually. We created a commercial program Total Visual Agent: that does it on a schedule with auditing and email notification if something goes wrong.

Third, if you are experiencing corruption after regular database maintenance, it's often caused by a suspect connection/user who disconnects in an improper manner. That can be very difficult to detect and replicate. We have a commercial product, Total Access Admin, that monitors the people going in and out of an Access database, logs that activity, and flags the people that exit improperly. If it's happening with the same person, there may be a hardware or network problem causing the corruption.

Finally, it may be possible that the corruption and performance problems are due to the front-end application. Bad code and techniques, corrupt objects, and other issues may be causing crashes and problems that lead to corruption. We address this in a few ways:

1. We adopt, implement, and detect/fix deviations recommended by Total Access Analyzer: Microsoft Access Best Practices Techniques

2. We implement global error handling that records crashes by users to text files so we have evidence of what failed. In addition to the procedure call stack, current procedure, error number and description, we also want the line number: http://www.fmsinc.com/free/NewTips/VBA/ErrorHandling/LineNumber.html This makes it significantly easier and quicker to reproduce and fix bugs.

Overall, it's about having a solid and repeatable process and checklist in place that evolves over time as new experiences are encountered.

Luke

Here's an article by FMS President Luke Chung that was recently published on the Entrepreneurs' Organization, Washington DC web site discussing the need of small business owners to Create a Backup and Disaster Recovery Plan for Your IT System. Luke has been a member of EO for over a decade and previously served as the Washington DC chapter president.

The article covers our experiences working with people running small to medium sized organizations, and the challenges they face to address business process continuation issues. An overview of the basic steps are covered.

Overview

As someone running a small organization, it’s easy to be consumed by day-to-day needs and neglect the importance of disaster recovery. Not only is it awful to think about, it requires detailed planning and thought to implement properly. Over the years, we’ve seen organizations run into problems because of inadequate planning and testing, so here are some pointers we’ve learned.

Disasters Happen

Unfortunately, bad things happen. You may have business insurance to pay for replacing your hardware, but for most organizations the value is in the data. Customer lists and purchase histories, accounting, inventory, operational reports and processes, management decision making systems, and even simple Word and Excel files can be critical to the success of your organization. Making sure these are accessible in the event of an emergency is worth considering before a disaster strikes.

Disasters can be on many levels. Certainly, there’s the chance of your whole building burning down but the more common situation is hardware failure or human error. All hardware eventually dies and a hard disk containing critical corporate information could just fail. Similarly, someone could make a mistake and accidentally delete data or files that need to be recovered. It may not threaten the whole organization but could be expensive and time consuming to recover.

Full Article

For Microsoft Access applications in particular, read our related article:  Creating a Backup and Disaster Recovery Plan for Microsoft Access Database Applications 

FMS President Luke Chung will be attending and speaking at the three day Microsoft Access conference sponsored by the Portland Oregon Access User Group.  Join him and other guest speakers including Alison Balter from InfoTech Services Group Inc., Armen Stein from J Street Technology, and Kevin Bell from Microsoft.

Luke will be speaking on the following topics:

• FMS Products for Microsoft Access Developers and How they Make You Money
• Microsoft Access and Azure: Working in the Cloud
• Microsoft Access Disaster Recovery Plans

For complete details visit PAUG 2011 Database Designer Conference and see you there May 14-16

File server databases like Access/JET MDB and ACCDB files need to be periodically compacted and repaired for optimal performance and to minimize database corruption. This also eliminates database bloat that can occur as data is added and deleted in the database. Here's a Microsoft Knowledgebase article that discusses some of the issues.

Microsoft Access databases can be manually compacted from the Access menu. Unfortunately, over the last few versions of Access, the location of this command has moved around driving Access many users and developers crazy. Read this paper for how performing this critical system administration task across all versions of Access:

Microsoft Access Database Compact and Repair to Minimize Corruption and Avoid Bloat (by Access Version)

If you create or take over a Microsoft Access application from someone else, you become responsible for the data in addition to the application and its queries, forms, reports, macros, and module code. Unanticipated disasters can occur, so it's important to prepare before they happen. The amount of effort and investment to spend for disaster recovery varies with the value of the application, but some basics apply to all applications.

A Disaster Recovery Plan is much more than making backups of your database. And if your backups are on the same machine and/or hard disk as your production database, that doesn't qualify. 

Read our paper on Creating a Backup and Disaster Recovery Plan for Microsoft Access Database Applications  for ways to limit your risk and headaches, while providing a professional solution for your end-users, boss, and clients.

Total Visual Agent is the system administration tool for professionally maintaining your Microsoft Access databases. Our seventh major release, Total Visual Agent 2007 is now shipping with full support for Access 2007 including the Access 2007 ACCDB database format. It incorporates many new features including improved support of Windows NT Service options, ADPs, and a more modern user interface.

Microsoft Access Jet Databases (MDB and ACCDB formats) need to be periodically compacted and repaired for optimal performance (visit the Microsoft site for keeping Jet databases working in top condition for more information). You also need to make regular backups or multiple backups for disaster recovery. Schedule these database chores, manage entire folders or disk drives, and ensure tasks are executed consistently and audited. If something goes wrong, Total Visual Agent can actually send you an email. You can also schedule your own tasks such as running a macro in a database, which can perform all sorts of work such as printing reports, downloads, file transfers, and other processes you want on a regular basis. Tasks can be run monthly, weekly, daily, hourly, or just one time. Schedule your tasks and confidently know they'll be executed according to your plan.