Jun 24

Microsoft Access Database System Administration

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 response 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 who 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.

Jun 21

Download the Free Preview of Total Visual CodeTools for Microsoft Office Access 2010

Download the free preview of Total Visual CodeTools 2010 for Microsoft Office/Access 2010. The Preview Version is fully functional through the end of July. Take advantage of this opportunity to see how Total Visual CodeTools can cleanup the code in your existing projects, let you deliver more robust solutions, and simplify your daily coding chores.

Integrated directly into the Integrated Development Environment (IDE), Total Visual CodeTools gives you a rich set of tools to help you create, cleanup, and deliver better solutions. The latest version is enhanced for MS Office/Access 2010 and has many new features:

  • Supports Microsoft Access/Office 2010, 32-bit version
  • VBA Code Parsing Supports Access/Office 2010
  • Code Cleanup and Code Delivery Allow Immediate Overwrite
  • Code Cleanup Error Enabler and Handler Tags are Customizable
  • Copy Control Builder Supports Multiple Target Controls
  • Long Text/SQL Builder Supports Query Retrieval and Converts Tabs and Spaces
  • Select Case Builder Supports Text Blocks and Numeric Ranges
  • Recordset Builder Defaults to Current Database and Simplifies Selections
  • Three Locations for Total Visual CodeTools on Your Menus
  • Tools Available During Debugging
  • Default 'Send To' is Remembered
  • Screens are Resizable
  • Redesigned Storage of Standards for Improved International Support

Visit these pages for:

Total Visual CodeTools supports all VB/VBA hosts, including Visual Basic 6.0 (VB6) and Office 2010, 2007, 2003, 2002/XP, and 2000.

Jun 13

Small Business Disaster Recovery Planning Article

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 

Jun 07

Microsoft Access and SQL Server Union vs. Union All Query Syntax

Microsoft AccessMicrosoft Access Union QueryMicrosoft SQL ServerThe purpose of the SQL UNION query is to combine the results of two or more queries into a single result set. The list contains all the rows belonging to all the queries in the union. This applies to queries in SQL Server or Microsoft Access. A common question is whether to use the UNION or UNION ALL syntax.

The main difference between UNION ALL and UNION is that, UNION only selects distinct values and sorts the results, while UNION ALL selects all values (including duplicates) without sorting. Read our revised paper on UNION versus UNION ALL SQL Syntax to learn more about the differences, see some examples, and understand why you would use one versus the other.

For more tips and using queries, visit our Microsoft Access Query Help Center.