Jul 29

Microsoft Access Developer Help Center Debut

We are very pleased to announce the creation of our new (and free) Microsoft Access Developer Help Center. It contains our many popular papers with tips and techniques for becoming a better Microsoft Access developer, programmer, and administrator. It covers the whole life cycle from strategic planning of how MS Access fits, database and table design, form and report tips, programming do's and don'ts, deployment, best practices, performance tips, etc.

This follows our recent Microsoft Access Query Help Center that's been well received, and our Microsoft Access to SQL Server Upsizing Help Center

We welcome any feedback on what we've put there and suggestions for additional content that would be helpful.

Jul 25

Replace the Paperclip Icon of Attachment Fields with a Column Name in Microsoft Access Tables in ACCDB Databases

Attachment Field Paperclip IconIn Access 2010 and 2007 databases (ACCDBs), the Attachment field type lets you attach files (documents, files, etc.) to records in your database. When you open the table in Datasheet view, the attachment fields display a paperclip as the column header.

Read our paper to learn how you you can Replace the Paperclip Icon of Attachment Fields with a Column Name in Microsoft Access Tables in ACCDB Databases .

Jul 20

Microsoft Access 2010 Service Pack 1 VBA Project Compatibility Issues

Microsoft released service pack 1 (SP1) of Office 2010 late June 2011. Information on the update and download instructions are in their Knowledgebase article 2460049. The KB article was last updated yesterday.

Unfortunately, we and some of our customers have encountered problems after installing the update. While we haven't had exhaustive testing with SP1 and tentatively believe everything is okay with the 32 bit version, there are definitely compatibility problems with the 64 bit version of Access 2010. In particular, ACCDE files created in the original release of Access 2010 64 bit no longer run under SP1. This error message appears:

The database cannot be opened because the VBA project contained in it cannot be read. The database can be opened only if the VBA project is first deleted. Deleting the VBA project removes all code from modules, forms and reports. You should back up your database before attempting to open the database and delete the VBA project.

Obviously, with an ACCDE file, unlike an ACCDB file, you can't modify the VBA project. It requires rebuilding the ACCDE from SP1.

So far, we have not experienced problems with our 32-bit ACCDE files between the original and SP1 Access 2010 versions. Some of these issues have been noted in these articles:

This is a Microsoft KB article discussing the problem which impacts ACCDE, MDE, and ADE files created with Access 2010, 64-bit. For us, when we create MDE and ADE files for use across multiple versions of Access, we've used Access 2003 so that still works fine. The only time we use Access 2010 64-bit version is when we're creating an ACCDE specifically for Access 2010 64-bit users.

This issue impacts two of our products' Access 2010 64 bit versions:

We have created new builds of these products and are undergoing internal testing and QA to verify they work with SP1. When ready, we'll release free updates of these products to existing customers. If you need it earlier, please contact our support team. Note that this does not impact the Access 2010 32 bit version.

Regardless of whether you are using our products or not, please be aware of the compatibility issues if you're using the 64-bit version of Access and deploying ACCDE files. You will need to make sure your Access version is in sync with your users' version. Unfortunately, this bug occurs before any of your code can run to provide instructions to your users or offer a graceful exit.

Not sure what version you have installed? Run Access, select the File menu, and click on Help. The version informing is shown on the right side and will show (SP1).

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.