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, Creating a Backup and Disaster Recovery Plan for Microsoft Access Database Applications for what we consider best practices.

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. Read our paper on Pinpointing the Exact Line Where a Crash Occurs in VB6 or VBA. 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 2011. 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 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.

Jun 01

Published on Enterprise Features: Microsoft Access Is Underrated–Your Hatred of Access Is Largely Unjustified

Microsoft AccessThe Enterprise Features web site highlight’s FMS President Luke Chung’s discussion about why Microsoft Access is underrated and hated in large enterprise organizations. “Haters Gonna Hate”.

He discusses the dynamics of IT departments in large organizations and their natural conflict with the needs and budgets of information worker. Enterprise FeaturesHe also shows how organizations that understand the strengths and weaknesses of Microsoft Access can leverage its power for competitive advantage, and how to structure service levels to do so.

May 31

New Microsoft Access Query Help Center

Microsoft AccessMicrosoft Access QueriesMicrosoft Access Queries are among the most powerful features of MS Access. We have created a new resource center to make it easy to review all our papers related to Microsoft Access Queries. These original works offer tips and techniques aren’t found anywhere else. They will help you maximize your use of Microsoft Access queries to better analyze and understand your data.

In the Microsoft Access Query Help Center you will find papers covering:

Retrieving Records

Modifying Data

Let us know what additional related topics you’d like us to add in the future.

May 31

Microsoft Access Database Scalability: How many users can it support?

What’s the Maximum Number of Microsoft Access Users?

Microsoft Access 2013There is a persistent myth that Microsoft Access Jet databases can only support 20 or so users. Here’s my response to a recent inquiry:

I flatly refute any suggestions that Microsoft Access users are limited to around 30. We’ve run many tests and have never seen that kind of degradation in performance. It is a myth from Access 2.0 days that was eliminated with Access 97 almost two decades ago.

A poorly designed Access database won’t support two users, but a well designed Access solution can support hundreds of users. Of course, what matters is the number of simultaneous users, and what they’re doing.

At Least 200 Simultaneous Microsoft Access Users on an Access/Jet Database

If everyone is just viewing data or entering data into a table, that takes very little work and a large number of people (well over 200) can be supported. People cannot type faster than what Access can handle. If users are all running massive reports and queries with data updates, that can still be done but performance would be an issue which applies to any technology, so testing and optimization would be necessary.

Migrate Back-End Access Database to Microsoft SQL Server for More Users

Microsoft SQL ServerIf the back-end database is in SQL Server rather than an Access/Jet database, the number of users can be practically unlimited if each user has their own front-end copy of the Access application. Performance issues still apply based on what they are doing. In some cases SQL Server is slower than Access, so it is important to understand the situation before thinking SQL Server is the answer.

All that said, any Access application that is distributed to others with shared data should be a split database design. Here are a few resources we’ve written:

Total Access StartupSimplify Support for All the Users of Your Access Databases

Total Access Startup helps with the distribution of databases to each user’s desktop, and launching it with the right version of Microsoft Access.

This allows you to centrally support a large number of Access users across your network and ensure everyone is using the latest version of your application. It also simplifies the migration from one version of Access to another.

Additional Discussions

This topic was also discussed on the Microsoft Developer Network, Microsoft Access Database Scalability: How many users can it support?

Microsoft

Apr 27

Now Shipping Total Access Emailer for Microsoft Access 2010

Microsoft Access Email BlasterTotal Access EmailerMicrosoft Access Email

Total Access Emailer is the most popular Microsoft Access email program. Running as an add-in, it lets you easily send personalized emails using the information in your database. From using fields in the subject and body of the message, to attaching PDF reports filtered for each recipient, you’ll find that Total Access Emailer revolutionizes the way you communicate with your contacts. Any table or query can be the data source for your emails which can be in text or HTML format. We can even automatically include graphics in your HTML emails or let them reference a public site.

We are very pleased to announce Total Access Emailer for Access 2010. Enhancements include support for Access 2010, 32 and 64-bit, compressing all your attached files into one zip file with optional password, international extended character support, a new Code Generator, additional programmatic interfaces, and many more new features. An X.6 upgrade version for Access 2007 and earlier versions also available. Download the Free Trial today!

Feb 23

Send Emails from the Preview version of Total Access Emailer for Microsoft Access 2010

Microsoft Access Email BlasterTotal Access EmailerMicrosoft Access Email

If you’ve ever wondered how Total Access Emailer could work for you, now is your chance to download a fully-functional version which includes the add-in and the VBA runtime library.

Total Access Emailer is the most popular Microsoft Access email program. Running as an add-in, it lets you easily send personalized emails using the information in your database. From using fields in the subject and body of the message, to attaching PDF reports filtered for each recipient, you’ll find that Total Access Emailer revolutionizes the way you communicate with your contacts. Any table or query can be the data source for your emails which can be in text or HTML format. We can even automatically include graphics in your HTML emails or let them reference a public site.

We are very pleased to offer a Free Preview of Total Access Emailer for Access 2010 (the preview version has expired and the Microsoft Access 2010 version is shipping). Enhancements include support for Access 2010, 32 and 64 bit, compressing all your attached files into one zip file with optional password, international extended character support, a new Code Generator, additional programmatic interfaces, and many more new features.

Feb 15

Luke Chung Speaking at Portland Access User Group Conference in May

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, 2011.