Updated BETA of PRO Version of 123 Error Handler Wizard
Add-In and Wizard for Microsoft© Access

Click on image to zoom in...

Download (580kb) the latest free BETA of 123 Error Handler PRO Wizard:

1. The Wizard reviews your database and identifies all procedures without error-handling, and reports the On Error... statement for all other procedures.
2. You select which procedures to add error-handling.
3. The Wizard adds error-handling to the selected procedures in one automated operation

The PRO version has these additional features:

• User-definable custom error-handlers, and
• Automatically add/remove line numbers to your code for enhanced error-handling.
  
• The June 2008 Update adds an Error Handling Status report to Access 2000+ versions

The most important part of error-handling is locating where in your code an error occurs. The PRO version of 123 Error Handler can help you pin-point the line of code where an error originates, and you can add or remove line numbers to a module with a single mouse-click.

Adding a Program to an Access 2007 Deployment Package

This new training video from Microsoft shows you how to add an add-in to an Access 2007 deployment package created using the Package Solution Wizard.

Dot vs. Bang: Put to Rest

The issue of whether two use a DOT(.) or BANG(!) when referring to a form field or control in Access VBA viz:

• Form1.Control1
• Form1!Control1

has been an issue of contention ever since Access 95.

Now Access MVP Michael Kaplan in a post on the Access Team Blog has provided a definitive guide:

• The dot gives you early binding and is resolved at compile time, the bang is resolved at runtime.
• In the case of Forms, both controls and fields in the underlying query can be referenced via a dot since they are all in the type library for the form.
• Also in the case of Forms, if you change the underlying query at runtime - dot references to the old query's fields will fail at runtime since update of the Form's type library cannot happen at runtime.
• Because the dot is early bound, IntelliSense happens by default with the dot, not with the bang.
• The dot (since it is early bound) is faster than the bang, but no one runs enough code that uses these items enough for the performance difference to actually matter.

I have been a long-time convert to the DOT based on the sheer convenience of the IntelliSense default being the DOT.

Latest Access Knowledge Base Articles

Courtesy of KBAlertz.com:

Access 2007

• 941877 Description of the Access 2007 hotfix package: February 26, 2008
• 948950 Description of the 2007 Office hotfix package: February 26, 2008
• 949946 Description of the Access 2007 hotfix package: March 15, 2008
• 943249 Description of the Access 2007 hotfix package: January 28, 2008
• 950277 Description of the Access 2007 hotfix package: March 22, 2008
• 949585 How to obtain the latest service pack for the 2007 Office suite
• 950488 Description of the Access 2007 hotfix update: March 31, 2008
• 951182 Error message when you try to install the Access 2007 Runtime: "Access 2007 Runtime: Another version of this product is already installed"
• 951339 Error message when you try to open the Office Customization Tool: "Files necessary to run the Office Customization Tool were not found"
• 950812 Description of the Office Access 2007 hotfix package: April 16, 2008

Access 2003

• 951646 Description of the behavior changes that occur after you install post-Office 2003 Service Pack 3 updates
• 950753 Description of the Access 2003 hotfix package: April 16, 2008
• 952609 Description of the Access 2003 hotfix package: May 19, 2008

Access 2007 Keyboard Shortcuts

You can now download from my aadconsulting.com Access resources page a complete list of all the Access 2007 Keyboard Shortcuts compiled as a searchable CHM Help file.

Access 2007 Bug Alert: Compact and Repair Might Delete Your Database

The Access Team Blog has just posted this advice:

KB article 950812: Compact and repair might delete your database (Access 2007)

KB article 950812 provides more information about a scenario where some people have reported that a compact and repair deletes their database. You can’t download the fix directly but can get it by calling CSS [MS Customer Service?]. This fix will be included in SP2 when that ships (I don’t have an ETA). Here is the most relevant part of the article:

In Office Access 2007, when you try to compact an .accdb database, you may receive the following error message:

Microsoft Office Access can't delete database name.accdb after compacting it. The compacted database has been named Database.mdb.

If you compact a database using the same name, Microsoft Access creates a new compacted database and then deletes the original database. In this case, however, the original database wasn't deleted because it is read-only.

If you can, remove the read-only status, delete the original database, and then rename the new database using the original name.
If you can't remove the read-only status, inform your workgroup administrator.

Additionally, after you click OK, you receive the following error message:

Could not find file 'database name.accdb'

Then, the original .accdb database and the temp-compacted Databasex.mdb database are removed unexpectedly.

This potentially catasrophic scenario is something we need to be mindful of whenever attempting a Compact & Repair in Access 2007.

I suggest you the close database, and manually back it up, and then do the Compact & Repair on the closed file and not in the open database.

In a comment to the Access Team Blog post, Russell Sinclair said on May 29, 2008 3:35 PM:

Glad to see you've posted this to the blog. This one killed quite a bit of work for me. FYI, there is a work around I detailed at one point to a team member. When the error message comes up, DO NOT click OK. Instead go to the folder and create a copy of the “Database.mdb” file before Access has the chance to delete it. You can then safely click OK and Access will destroy the original Database.mdb database.

Update 2 June 2008: New Access 2007 Backup Before Compact Tool

I have responded to this issue with Backup Before Compact, a free Access 2007 Add-In that I have developed that when invoked automatically closes Access to unlock the database file, backups, and then compacts & repairs the database before re-opening it in Access 2007. A fail-safe feature renames the original file, which is retained as a temporary redundant backup until the tool is next used on the database.

You can download the Add-in from my Free Access Tools page.

Access Help and How To Links at Office On-Line

On-Line Help

• Access 2007 Help
• Access 2003 Help
• Access 2002 Help
• Access 2000 Help

Training

• Access 2007 Training
• Access 2003 Training
• Access 2007 Demos
• Access 2003 Demos
• Access 2007 Webcasts

Templates

• Templates for Access

Latest SQL Server KB Articles

An ongoing MS DTC transaction is orphaned in SQL Server 2005

Microsoft does not provide support for SQL Server TDS enhancers

On Demand Web Seminar: True High Availability for SQL Servers

This free interactive web seminar from WindowsIT Pro on high availability on SQL Servers covers:

• WAN HA support
• HA Support for Standard vs. Enterprise Edition
• Creating a HA cluster including majority node set (MNS)
• How to remotely install HA solutions on your servers from a central location
• Why you need replica servers, and how to automatically configure them
• How to test and validate your replica environment without operator intervention
• Using the Rewind function (Continuous data protection) included in CA XOsoft High Availability
• Disaster Recovery Planning

Register here

Tips on Controlling the Access Application Window

You can use some simple API calls from VBA to manage the Access Application window when you want finer control on how your Access application opens or is displayed:

1. Make the Access application window invisible, so your application looks like an executable. Note: All menubars/toolbars/ribbons will also be hidden.
   
   
2. Resize the Access application window to fit your start-up form and place it at specified screen co-ordinates.

I have prepared two sample MDB's (for Access 2000 or later) to display each of these techniques, and they are available for free download here.

Latest SQL Server Fixes

FIX: An ongoing MS DTC transaction is orphaned in SQL Server 2005

FIX: The transactional replication does not run the corresponding stored procedure at the subscriber when the stored procedure is run at the publisher in SQL Server 2005

Expanded List of Formats to be Supported in Office 2007 SP2

Microsoft has announced that the release of Microsoft Office 2007 Service Pack 2 (SP2) scheduled for the first half of 2009, will include support for XML Paper Specification (XPS), Portable Document Format (PDF) 1.5, PDF/A, and Open Document Format (ODF) v1.1.

When using SP2, Office users will be able to open, edit and save documents using ODF and save documents into the XPS and PDF fixed formats from directly within the application without having to install an add-in. Users will also be able to set ODF as the default file format for Office 2007. To also provide ODF support for users of Office XP and Office 2003, Microsoft says it will continue to collaborate with the open source community in the ongoing development of the Open XML-ODF translator project on SourceForge.net.

SQL Server Data Types

10+ Common Questions about SQL Server Data Types SQL Server offers a variety of data types, and choosing the right one can be confusing. Download this White Paper from Susan Harkins of TechRepublic.com where she answers common questions about using SQL Server data types and explains their optimum usage.

Access 2007 Security

These two articles from Microsoft will give you an overview on how security in Access 2007 differs from previous versions:

• Getting Started with Access Security
• Encryption vs. Encoding

How Custom Access 2007 Ribbons Work



The most challenging issue for Access users and developers when upgrading to Access 2007 is the radical shift from CommandBars to the RibbonBar paradigm.

In this outline I will introduce the operation of the Ribbon in Access 2007, provide some guidance on customisation, and point you to various tools and on-line resources to assist your efforts at Ribbon customization.

Getting Started:

1. Download and install my Access Ribbon Switchboard Database demo database.
   
   
2. Start Access 2007. Open the Access Ribbon Switchboard Database using the {SHIFT} key.
   
   
3. Click the Microsoft Office Button, click Access Options, and then click the Advanced tab.
   
   
4. In the General section, select the option Show add-in user interface errors, when debugging a RibbonBar.
   
   
5. Click OK to close the Access Options dialog box.
   
   
6. Right-click the Navigation pane. Point to Navigation Options, and then click Show System Objects. Click OK.
   
   Note: The Access system tables appear in the Navigation
   pane. In the Demo Database the system table, USysRibbons,
   each record contains the XML for each custom Ribbon in the
   database.
   
   
7. Click the Microsoft Office Button, and then click Access Options.
   
   
8. Click the Current Database tab, in the Navigation Pane and Ribbon and Toolbar Options sections, you will see that the following option settings hide the Database Window, the Navigation Pane, and the Full Menus, and set the custom ribbon, Main, as the StartUp Ribbon Bar:
   
   
   
   
   
9. Click OK to close the Access Options dialog.
   
   
10. Close the Demo Database.
    

11. Re-open the Demo Database without using the {SHIFT} key.
    

12. The Ribbon UI displays the Main Ribbon Custom Tab:
    
    Note: You use a Ribbon from the USysRibbons table to
    supply the UI for a specific form or report by setting the RibbonName
    property to the name of the Ribbon record in USysRibbons table.
    
    
    
    

Creating the XML Ribbon Definitions:

The ribbon definition is a simple XML file and can be easily created in any text editor, but if you use the free Microsoft Visual Web Developer 2005 Express Edition (VWD), you can get IntelliSense for your code. You can point it at the custom ribbon schema at http://schemas.microsoft.com/office/2006/01/customui and VWD should pick up the correct file, which is called customUI.xsd. You can download the file from http://officeblogs.net/UI/customUI.xsd. This enables VWD to provide type-ahead IntelliSense for correct ribbon xml:

The contents of the XML file are:

1. customUI - this is the top level element for a custom ribbon.
   
   
2. ribbon - defines the ribbon itself. Set the startFromScratch attribute = "true" to create a new blank ribbon. If this attribute is omitted or left blank, the custom ribbon will merge with the existing Access ribbons.
   
   
3. tab - creates a new tab in the ribbon
   
   
4. group - creates a group. These are used to logically group controls in the ribbon.
   
   
5. id - unique name of a control in the ribbon label - static text displayed with a control
   
   
6. button - similar to a command button. Use the onAction attribute to specify the name of a macro in the Ribbon macro in the Demo database which will be called when the button is clicked.
   
   
7. dropDown - creates a dropdown list that cannot be updated. To create a drop down that the user can type in, use a comboBox control instead.
   
   
8. imageMSO - specifies the name of a built-in image control in Office 2007 that can be used as in icon in your ribbon. You can find the Image name or ControlID by hovering your mouse over a control image in the Customize the Quick Access Toolbar dialog, and reading the screentip for the control image. For example, the ControlID for the Form icon image is CreateForm:
   
   
   
   

Reviewing the Ribbon XML:

On aadconsulting.com I provide for free download two tools that will help you to debug the XML for your custom ribbon bars:

• Office 2007 Ribbon XML File Reviewer
• Access 2007 USysRibbons Reviewer Add-In
  

These freeware tools providing advanced Office 2007 Ribbon XML file review features. Open any Office 2007 Ribbon XML file in the XML File Reviewer to review an Office Ribbon's full menu hierarchy in a tree-view with all control attributes detailed in an adjacent panel. The Access 2007 USysRibbons Add-In reviews and previews the custom Ribbons in the USysRibbons table of an Access 2007 database. Extensive reference resources are incorporated into the GUI's of both products. Both tools require Access 2007.

Update 9 June 2008: Check out the Office 2007 RibbonCreator WYSYWIG tool from http://www.ribboncreator.de/en/

More Information:

• Access 2007 Ribbon Attributes
• Access 2007 Ribbon Class
  
• Customizing the New Access UI
• Extend The 2007 Office System With Your Own Ribbon Tabs And Controls
• Office RibbonX API- Extend The 2007 Office System With Your Own Ribbon Tabs And Controls
• Ribbon Extensibility in Access 2007
• Office Custom UI Editor
• Customize the Office (2007) Ribbon UI for Developers (Part 1)
• Customize the Office (2007) Ribbon UI for Developers (Part 2)
• Customize the Office (2007) Ribbon UI for Developers (Part 3)
• Access Freak Access 2007 Tutorials
• AccessRibbon.com
• Ribbon Customizer

Free MS Access Databases and Templates

For those who are not aware of the many free databases available for download from aadconsulting.com this post features all current downloads with direct links.

Recent Additions:

Access 2003 versions of the Advanced Issues Tracker database, and a new Case (Project) Manager Access 2003 database.

There is also a new Donations database for Access 2000 or later.

All these databases have modern GUI's and enhanced functionality. All except the Donations MDB use only embedded macros and are aimed at introducing new users to the code-free capabilities of Access. All code is exposed.

Other Open Code Databases:

These databases can be run in Access 97/2000/2002/2003/2007.

Service Call Management Developed using the Database Wizard -tracks workorders, parts, and labour inputs. Invoice generater etc. Additional code adds inventory tracking. Remove sample data before use.

Managers For divisional managers. Review managers and staff by division. Remove sample data before use.

Resumes For personnel managers. Review staff and resumes by region. Remove sample data before use.

Client & Contact Manager Based on the Access97 Database Wizard -tracks all clients and contacts, import data from Outlook, mail-merge and more. Additional code adds client data and admin functions. Remove sample data before use. Access2000 version

Subscriptions Manager Tracks subscribers by name and organisation, label and envelope wizards, mail-merge and more. Remove sample data before use. Access2000 version

FREE Fully Featured Applications:

The source code .mdb for the following databases can be purchased for only US$40 each. Secure payment with PayPal. Files will be e-mailed. Order

These databases can be run in Access 97/2000/2002/2003/2007. Download the Access2000 versions to run in Access 2000/2002/2003/2007.

SMS Call Logging DB Application NEW Updated GUI for Access 2000 version (Download includes both Access97 and Access2000 versions) SMS - Service Management System - is a fully integrated and comprehensive Service/Maintenance Contract Call and Revenue Management Database Application.

FreeTLT HRIS DB Application NEW Updated GUI for Access 2000 version (Download includes both Access97 and Access2000 versions) Free TLT is a comprehensive HRIS application that tracks employee leave and training, with training profiles and extensive reporting. Review leave and training in the daily, weekly, and monthly look-aheads. Use the exclusive TLT SmartToolbar to filter and find records in all forms. Full Details

Mailing List Manager NEW Updated GUI for Access 2000 version Features a new Access navigation paradigm. Manage large mailing lists, print labels, envelopes, export data to delimited files for mail merges, check data integrity, and advanced filtering. Access2000 version

Document Tracker NEW Updated GUI for Access 2000 version With an optional automated Word front-end. Document management system. Automatic document referencing and creation. Version 2.3 adds the DokTrack.dot Word template and adds a Title field to the table tblAuthors. The Title for the selected Author is automatically picked up in a new Word document based on the template. Users can now use use Word to create and manage documents without opening Access. Access2000 version

Meeting Tracker NEW Updated GUI for Access 2000 version Create Tasks, allocate responsibility to a Task Owner, assign people to Task Teams, record meetings and attendance. An instant Meeting Summary Report can be printed, emailed or exported to Word or Excel. Access2000 version

Program Manager NEW Updated GUI for Access 2000 and Access 2002 versions A complete business solution. Manages Clients, Contacts, and Client Programs. Allows multiple contacts for each client, and individual address and contact details for each contact. Nominate separate client program and invoicing contacts. Full reporting and database maintenance. 22May01- new version allows update of Countries field in address table. Access2000 version or Access2002 version

Project Manager NEW Updated GUI for Access 2000 version Fully featured business application for project tracking and control in a business unit. Access2000 version

Sales Manager NEW Updated GUI for Access 2000 version The Sales Manager is a fully-featured sales management application with comprehensive reporting and product-costing functionality. Access2000 version

Contractor Tracker NEW Updated GUI for Access 2000 version The ultimate contractor tracking and control application. Remove sample data before using. Access2000 version

Webcast: Essential SQL Server 2008 for Developers
Sponsored by Microsoft May 12 - 15, 9:00 a.m. PDT

SQL Server 2008 offers an impressive array of capabilities for professional developers that build upon key innovations introduced in SQL Server 2005. The Essential SQL Server 2008 for Developers Webcast Series will help you understand how to build enterprise-class applications which deeply exploit the rich data types, programming models and new development paradigms in SQL Server 2008:

	Part: 1 TSQL Improvements And Data Types	5/12/2008 09:00 AM PDT
	Part: 2 Beyond Relational	5/12/2008 11:00 AM PDT
	Part: 3 Service Broker And Messaging	5/13/2008 09:00 AM PDT
	Part: 4 .NET Code In SQL Server	5/13/2008 11:00 AM PDT
	Part: 5 Client Programmability	5/14/2008 09:00 AM PDT
	Part: 6 ADO.NET Entity Framework	5/14/2008 11:00 AM PDT
	Part: 7 HTTP Programming And SQL Server	5/15/2008 09:00 AM PDT
	Part: 8 Occasionally Connected Systems	5/15/2008 11:00 AM PDT

NEW Dashboard Demo
for Microsoft© Access 2000/2002/2003/2007

Screenshot from Access 2003 in Windows XP: click on the image to zoom in

I have just uploaded my MS Access Dashboard Switchboard Sample Database, which uses only native Access controls, and is a fully functional demo database featuring an integrated dashboard switchboard with easy single screen navigation and advanced usability and filtering:

• Filter by user-selected fields
• Fully-integrated cascade update record editing
• Full find first and find next functionality

Download the DEMO: Access Dashboard Demo

You can use the demo as an Issues Management Application or buy the source code for only U$39.95 to use the GUI in your own Access applications.

UI Builder for Access

Just came across this product, UI Builder for Access from OpenGate Software, and from the on-line demo was very impressed with the tool's wide functionality and the clean gui produced by the Builder. No VBA knowledge required but pricey. Download the sample databases from here.

Latest SQL Server Fixes

FIX: The SQL Server service may stop responding when you run a query that contains many OR clauses in SQL Server 2005

FIX: The update operation is performed on incorrect rows when you update a column of the VARCHAR data type on a remote table by using a linked server in SQL Server 2005

FIX: The SQLGetData function may return zero for the actual size of the data that is to be copied when you use the function to retrieve data for a binary column in SQL Server 2005

FIX: The application seems to stop responding when an application executes a large query that has parameters in predicates in SQL Server 2005 Compact Edition