aadconsulting.com On-Line Access Knowledge Base Upgraded













I have upgraded the on-line Access KB Articles database for direct search results for over 3,000 article links. The search page now supports OR & AND boolean operators.

Using the latest Technology in MS Office

These recent articles from MSDN will give you a head start on the new features of Office 2003:

Word 2003: Importing XML Data to Word 2003 Using Visual Studio Tools for Office Download this code sample to learn how to import XML data from a database into Word documents using Visual Studio Tools for Office 2003.

Office COM Add-Ins Use Visual Basic to build COM add-ins and then use them to extend your Outlook applications. These techniques can also be applied to developing COM Add-Ins for Access.

Begin Web Part Development in Windows SharePoint Services Introduce yourself to Web Part programming using prewritten Web Parts, Web Part Pages, custom lists and libraries, browser-based editing, FrontPage-based editing, Data Sources, Data Views, Web Part connections, and Microsoft Office connectivity.

For the Serious Office Developer

Valuable Access developer resources from MSDN:

• Microsoft Office 2000-Visual Basic Programmer's Guide
• How to Use Command-Line Switches in Microsoft Access
• Basics for Building Microsoft Office Access 2003 Runtime-Based Solutions

Get Started on Access VBA Error-Handling

These overview articles from MSDN on implementing error-handling in Office applications are a good starting point for new developers:

• Elements of Run-Time Error Handling
• Basic Error Handling

Get A Handle on MS Access Security

A collection of articles on Access security from MSDN:

• About Protecting a Microsoft Access Database
• Access Users and Groups
• Creating Secure Data Access Pages

Deploying Your MS Access Database Applications

Three new articles from MSDN:

• Preparing Your Access 2003 Database for Deployment, Part 1
• Preparing Your Access 2003 Database for Deployment, Part 2
• Packaging Access 2003 Solutions

New BETA 2 of Access Table Links and Connections Manager:
Manage MS Access Table Links and Project Connections More Easily

The BETA 2 is now available for free download. The Links Manager will find all the MS Access files in the location you select and an Access report will display all table links and base string connections, and any connect errors grouped by:

• Jet Version
• File Type: mdb/mde/mda or adp/ade
• Linked Database Paths and linked tables: for mdb/mde/mda's
• Base Connection String: for adp/ade's
• Separate reports will be produced for .mdx files and .adx. files

BETA 2 adds a new dependents of report, after feedback on the first BETA.

MS Office 2003 Service Pack 2 Out








Office 2003 SP2 was realeased on 27 Sep:

• Download
• What Is In SP2?
• Access 2003 Fixes in SP2

Ezy Reporter PLUS Add-In for MS Access BETA

The easy way to create data output from MS Access - including XML - with my latest Access Add-In, Ezy Reporter PLUS. An add code options allows developers to add Ezy Reporter PLUS to their applications. Download from aadconsulting.com

Ezy Filter PLUS Add-In for MS Access BETA

Easily filter any data form or report in any MS Access database, with advanced sorting options using my latest Access Add-In, Ezy Filter PLUS. An add code options allows developers to add Ezy Filter PLUS to their applications. Download from aadconsulting.com

Latest MS Access KB Articles

Courtesy of KBAlertz.com:

Access 2000
905726 You are unexpectedly prompted to restart the computer after you install an Office service pack or an Office update

Access 2002

906680 Description of the Microsoft Office XP Proofing Tools Kit hotfix package: September 1, 2005
906886 Description of the proofing tools for the Dutch version of Office XP post-Service Pack 3 hotfix package: September 1, 2005
902349 How to change the source for a client computer from an updated administrative installation point to an Office 2003 or Office XP original baseline source
904948 A document may take a longer time than expected to print or the print spool file may be larger in Office 2003 or in Office XP

Access 2003

907339 Description of Photo Editor support with Office 2003 programs
905476 You cannot update the data in a view that you created in Office Access 2003
902988 How to perform a chain installation of Office 2003 and Office 2003 updates

NEW Contemporary Switchboard And Transaction Log Demof or Microsoft© Access 2000/2002/2003

















I have just launched my latest Access Demo Database featuring a new look sidebar application navigation with bonus comprehensive transaction logging code.

Kids Programming Language (KPL)

Morrison Schwarz have also developed a free and really cool development language especially designed to gets kids started in programming. KPL is an easy-to-use VB-like language to make learning to program fun. Download from Kid's Programming Language. Read the wrap on KPL from MSDN's CoolApps on the Coding4Fun portal.

Morrison Schwartz: Free Developer Tools

I just discovered this excellent resource. Morrison Schwartz offer a wide range of free Developer Tools.

More on Office 12


TechRepublic.com has some feature articles on Office 12:

• FAQ: Looking into Office 12 Here's what you can expect from Microsoft's radical revamp of Office, due next year.
• Office 12 makeover takes on 'feature creep' Microsoft's radical Office 12 overhaul, due next year, aims to counteract "feature creep" and wake users up to the suite's hidden treasures.
• Photo gallery: New looks for Vista, Office The September 2005 edition of Microsoft's Professional Developers Conference got under way with a keynote address by Bill Gates and a "live preview" of the next version of Office.
• Blog: Microsoft Office 12--The rise of smart applications? Find out why TechRepublic's Jason Hiner is ambivalent about Bill Gates' statement at the Professional Developers Conference that Office 12 will attempt to automatically adapt its interface to the type of work that a user is doing. However, he adds, "This makes me think that we may be seeing the beginning of the smart applications of the future--applications that will be able to anticipate and help streamline rudimentary tasks."
  

Diving Into the New Office 12

A 40 minute video demo of the new Office GUI.

Links to Free Microsoft Access and Microsoft Office Training


I have added a new Free Access Training page to aadconsulting.com, with many links to free MS Access and MS Office Training, eBooks, and Book and Tutorial Downloads. There also links to Office, SQL Server, Web Development, VB, and XML material.

Office 12: Goodbye to Menubars and Toolbars...

Instead of the menubar and toolbar, Office 12 users will navigate a new user interface referred to as a ribbon. This new navigation bar consists of a tabbed command bar, which will open galleries in place of the venerable toolbar by hovering over command items. Full review and screenshots.

Office 12 Previews

Office "12" XML Schema Reference - PDC 2005 Preview This download contains DRAFT documentation on a selection of Microsoft Office 12 XML Schemas.

Video: Brian Jones on New Office XML Format Brian Jones, program manager on the Microsoft Word team, demos the new Office XML format.

Automating the Deployment of an Office InfoPath Form Template

Automating the Deployment of an Office InfoPath Form Template How to update part of a previously-published form template and move it to a new location, such as a Web server or a network file share.

Plenty of MS Office Hype But Where is the Support?

For Microsoft's fiscal year ended June 30, the unit that includes Office had operating income of nearly $8 billion, on revenue of $11 billion. The company's overall revenue was $40 billion. It is fair to say that Office is the flagship earnings product for Microsoft, but as an Office developer I feel short-changed.

While massive resources and support are available for VB.Net, Visual Studio, and SQL Server, Office Developers get short shrift. Most Office applications are deployed in small businesses and within corporate units, so the fancy but complex and costly-to-maintain enterprise interfaces offered by VSTO and SQL Server are not relevant, yet the support focus is on these enterprise tools. All the talk about supporting Office Developers is just that.

A case in point: I applied for an MS Office ISV "buddy" under the ISV Microsoft Buddy Program, where an MS specialist is assigned to you as a direct MS support contact. Nothing happened for 6 months, and on enquiry was told a match could not be found! I suppose they were embarrassed and soon came up with a "match", who turned about out be an evangelist for VB.Net with no real interest in Office...

New GUI for Office 12

At this week's PDC05 the new UI for Office 12 was showcased. A Q&A article on the new design is available at the Office Developer Center on MSDN.

Easily Build Custom Controls for Your Office Applications

In Visual Studio Tools for Office, Windows Forms makes creating a custom control as easy as building a form. From DevX.Com.

Office 12 Screenshots

Go to http://bink.nu/Article4818.bink

Visual Studio for Office: MSDN Webcast

Visual Studio for Office: MSDN Webcast Forum with MS Office Development Team Explore the new features of Visual Studio 2005 Tools for the Microsoft Office System, such as hosting Word and Excel as full-fledged designers within the Visual Studio development environment, programming directly against data and view objects, and creating a custom Document Actions Pane. Developers can utilize these new features to build solutions that work with the current and upcoming versions of Word and Excel.

Use MS Office to Track Your Stocks

A simple way to achieve this is to use Excel and Access in MS Office 2002 or Office 2003.

Excel's Web Query feature allows a spreadsheet to be automatically updated by data from the Web each time you open the file. You can set up a Web query in a spreadsheet that will give you the latest stock prices from a web site:

1. Open a blank worksheet and click in cell A1.
2. From the Menubar click Data Import External Data New Web Query. Excel will then prompt you for an Internet connection if you're not already connected.
3. In the Address box, enter the URL of the Web page that contains the stock statistics and then click Go. For example the live stock statistics on the freerealtime.com site are easily linked.
4. Click the yellow boxes with arrows next to the blocks of text you want to import. When clicked, the yellow boxes turn into green boxes with check marks.
5. Click the Import button.

To have the Web query update the spreadsheet every time the file is opened:

1. Right-click a blank area of the main toolbar and select External Data.
2. Click the Data Range Properties button.
3. In the Refresh Control section, select Refresh Data On File Open. Click OK.
4. Add column headings (ie. field names) to each data column as needed.

Now the spreadsheet will automatically update the web data each time the file is opened, or accessed by another Office application such as Access.

If you need to take a snapshot of the data in Excel:

1. Select the data you want to copy. From the Menubar click Edit Copy.
2. Click on the cell where you want the snapshot of the data to appear.
3. From the Menubar click Edit Paste Special.
4. Click the Values And Number Formats checkboxes, and then click OK.
5. From the Menubar click Edit Paste Special.
6. Click the Column Widths checkbox and then click OK.

Finally you can use this date in Access by creating a new database in Access and then:

1. From the Menubar click File Get External Data Link Tables...
2. In the Link dialog, select Microsoft Excel (*.xls) from the Files of type combobox, and click Link.
3. Follow the the Link Wizard to finish, and you will have a table linked directly to your spreadsheet.

Data Access: Let Your MS Access Users Sort it Out

Data Access: Let Your Users Sort it Out Develop an MS Access solution that allows users to sort the data in their forms (or subforms) that you can add to your application easily, and along the way, the characteristics of a well-designed module.

Exclusive: A Better ZoomBox for Access

Exlcusive to readers of the Access Extra blog, is my new free tool for Microsoft Access versions 97 to 2003: A Better ZoomBox to edit the following control properties when designing Access Forms and Reports:

• Record Source
• Row Source
• Control Source

Features:

• Customization saved from session-to-session:
  
  - font (Arial/Courier New/MS Sans Serif/Tahoma)
  - font size (8-14) - font bold (y/n)
  - Enter Key behavior can be set to "Enter New Line" (y/n)
  
• Find/Replace functionality

The Better ZoomBox is invoked by clicking the <...> build button next to each of the above properties in form and report design view.

Office Online Training Courses Quick Reference

Office 2003 Publication: Office Online Training Courses Quick Reference Cards Content This download provides you with complete text and illustrations of all Quick Reference Cards in Training courses published before May 2005.

Tip: Shortcut to Check a Form's Name in MS Access

In the Query Design window, when invoked from within a form's design view in MS Access, you can't go to the Database Window to check the name of an object while designing a parameter query, and as I do, copy and paste the name into the query (using and then cancelling Rename). A workaround is to invoke the Startup dialog by clicking Tools, Startup... from the Menubar, where you can view all forms in database from the Display Form/Page combobox, select the form in the combox, copy the name, and then click Cancel.

Role Agent for Microsoft Office Live Communications Server 2005

Role Agent for Microsoft Office Live Communications Server 2005 Role Agent is a sample Microsoft Windows service application that demonstrates the use of the Real-time Communications API 1.3 and Microsoft Office Live Communications Server 2005 to support role-based instant messaging (8/16/2005).

Latest MS Access KB Articles

Courtesy of kbalertz.com:

Access 2000

• 905248 Custom changes that you made in the HKEY_CLASSES_ROOT subtree are overwritten after you update or repair the Office installation

Access 2002

• 904411 Description of the Italian Proofing Tools for Office XP post-Service Pack 3 hotfix package: July 15, 2005
• 903776 Office updates are not successfully installed on some client computers when you deploy updates by using Windows Server Update Services
• 905530 FIX: When you create a database or a table, an error occurs and Access unexpectedly closes in Office Access 2003 or in Access 2002

Access 2003

• 903772 You receive an "Error 80070643" error message when you try to update Office 2003 by using Software Update Services
• 03074 BUG: A record in a linked SQL Server 2000 database table is not correctly updated when you modify the record in an Access 2003 database table

Latest MS Office Downloads

Excel 2003 Sample: Populating Worksheets with Multiple Related Records from Windows Forms Use this sample to to display Microsoft Windows Forms from a Microsoft Office Excel 2003 workbook, display related records, and write that information into formatted worksheets.

Visual Studio 2005 Tools for Office Beta 2 Sample: Migrating a Word VBA Solution to Visual Basic Using Visual Studio 2005 Tools for Office VBA Migration Case Study based on Microsoft Word 2003, for experienced Office/Visual Basic for Applications developers who are taking their first steps with Visual Studio 2005 Tools for Office.

Tip: Use ADO wildcards in the Access Query Design Grid

In MS Access 2000 or later, if you're using ADO, there is a way to use ADO wildcards in the Access query design grid. Instead of using the Like operator, use ALike, which also works in SQL statements used in VBA code.

Manage MS Access Table Links and Project Connections More Easily

My new MS Access tool, Access Table Links and Connections Manager V1.0 BETA, is now available for free download. The Links Manager will find all the MS Access files in the location you select and an Access report will display all table links and base string connections, and any connect errors grouped by:

• Jet Version
• File Type: mdb/mde/mda or adp/ade
• Linked Database Paths and linked tables: for mdb/mde/mda's
• Base Connection String: for adp/ade's
• Separate reports will be produced for .mdx files and .adx. files

Microsoft Releases XML 3.0 Update (SP7)

MSXML (Microsoft XML Parser) 3.0 Software Development Kit (SDK) The Microsoft® XML Parser (MSXML) 3 SDK includes header and .lib files, and documentation for the MSXML 3 Release.

Microsoft XML Parser (MSXML) 3.0 Service Pack 7 (SP7) The Microsoft® XML Parser (MSXML) 3.0 SP7 release offers a number of bug fixes over the previous MSXML 3.0 SP releases.

The Deal on Deploying Windows XP and Office 2003

Business Dessktop Deployment Standard Download planning, building, and technical guidance, templates, and scripts to facilitate deployment of Microsoft® Windows® XP Professional and Office Professional Edition 2003.

Business Desktop Deployment Enterprise Download planning, building, and technical guidance, templates, and scripts to facilitate deployment of Microsoft® Windows® XP Professional and Office Professional Edition 2003 through Zero Touch deployment.

MS Office 2003 Sample: Smart Documents Development Overview

Office 2003 Sample: Smart Documents Development Overview Learn how to create customized, context-sensitive task panes that interact with XML-based files in Microsoft Office Word 2003 and Microsoft Office Excel 2003.

The Latest for Office Developers from MSDN

Register for the Microsoft Professional Developers Conference 2005 Join developers at the Microsoft Professional Developers Conference 2005, where the next waves of Microsoft Windows and Microsoft Office System developer capabilities will be unveiled.

Use Excel 2003 to Create and Manage Windows SharePoint Services Web Sites How various aspects of Visual Studio 2005 Tools for Office, Excel, and Windows SharePoint Services work together in a hypothetical business scenario.

Outlook from the Managed World Get started with building add-ins for Outlook 2003 using the Visual Studio Tools for Office.

New Revision Tool for Word

Office 2003 Add-in: Word Redaction By using the Word 2003 Redaction Add-in, you can mark sections of Microsoft Office Word 2003 documents for redaction. You can then create a new, redacted version of the document in which this marked text is unavailable. ReadMe file

Latest MS Access KB Articles

Courtesy of kbalertz.com:

Access 2000

• 903275 How to troubleshoot problems that you may experience when you try to activate an Office product

Access 2002

• 903773 No updates are displayed when you scan for updates by using Microsoft Update or Windows Software Update Services after you connect to a Microsoft Office administrative installation point to reinstall Office
• 903771 Information about the ability to uninstall Office updates
• 903097 The installation state of each Office XP program is shown as "Not Available" when you try to perform a custom installation of Office XP or of an individual Office XP program
• 904411 Description of the Italian Proofing Tools for Office XP post-Service Pack 3 hotfix package: July 15, 2005

Access 2003

• 903772 You receive an "Error 80070643" error message when you try to update Office 2003 by using Software Update Services
• 904427 You receive a "Norton AntiVirus 2005 does not support the repair feature" error message when you start an Office 2003 program

Expense Reports Made Easy with MS Office Solutions and InfoPath

Expense Reports Made Easy with Microsoft Office Solutions Video overview of the MS Expense InfoPath client.

MSN Toolbar With tabs for Windows Explorer

If I am not the first to relate this, please excuse my ignorance...

As I became increasingly frustrated with multiple Windows Explorer windows on my Windows XP Desktop, and having to drill down anew to my usual folders in each Windows session, I thought there must be a better way to manage Windows Explorer folders. I thought that if you can have tabbed web browsing, why not tabbed file browsing.

So I started playing with the new tabbed windows browsing functionality in the new MSN Toolbar for Internet Explorer, and discovered that you can setup Windows Explorer folder tabs in IE and save them:

You proceed as follows:

1. Click the New Tab button to create a new tab in IE
2. In the IE Address Bar type/paste the root path of the folder, for example, g:\data, and hit Enter
3. The new tab will go to the folder and display a Windows Explorer window
4. Continue until you have added all your favorite folders as separate tabs
5. Click the MSN Toolbar Home button and click the Set Current Pages as My Tabs button:
   
   
6. In you next Windows session click Open My Tabs to load all your folder tabs... very neat :)

Free BETA of SmartForm ADP for Access2000/2002/2003 ADP Projects

Try the Free BETA of my latest MS Access Add-In: SmartForm ADP for Access2000/2002/2003 ADP Projects.

Using Excel 2003 to Manage Project Sites with Windows SharePoint Services 2003

Using Excel 2003 to Manage Project Sites with Windows SharePoint Services 2003 How to leverage Microsoft Office Excel 2003 and Visual Studio 2005 Tools for Office to create and manage Microsoft Windows SharePoint Services Web sites.

MSDN Webcast: Visual Studio Tools for Office

MSDN Webcast: Visual Studio Tools for Office Get an overview of Visual Studio Tools for the Microsoft Office System (VSTO) and how it supports building smart client applications for Office. This webcast will begin with a discussion of Microsoft Office-based smart client applications.

Start Time: Wed, Aug 03, 2005 9:00 AM (GMT-08:00) Pacific Time (US & Canada)
End Time: Wed, Aug 03, 2005 10:00 AM (GMT-08:00) Pacific Time (US & Canada)

MS Access Keyboard Shortcuts

70+ Keyboard shortcuts to move faster in Microsoft Access A download from TechRepublic.com.

Create a Capital Expenditure Model with Visual Studio 2005 Tools for Office

Create a Capital Expenditure Model with Visual Studio 2005 Tools for Office Learn how to take a sophisticated Excel 2003 spreadsheet model and turn it into an intuitive, easy-to-use, integrated application using Visual Studio 2005 Tools for Office. Article from MSDN.

Visual Studio 2005 Tools for Office—Outlook Beta

Download: Visual Studio 2005 Tools for Office—Outlook (Beta) Outlook's add-in support for Visual Studio 2005 Tools for Office Beta 2 provides tools to develop, debug, and deploy solutions simply and quickly—including a DLL to tie managed add-ins to Outlook. This provides a simpler framework for creating managed add-ins with improved security.

MS Access Database Mechanic

Further to my post of May 24, I have developed a new freeware tool, Access Database Mechanic, that automates the process of attempting to recover a corrupt MS Access database, using three operations: compact & repair , decompile, and completely rebuilding an .mdb. The applet also features a fail-safe mechanism that creates a uniquely named back-up copy of the .mdb before any recovery operation is attempted. The Beta 1 download includes versions for Access97/2000/2002/2003.

MS Access Scalable with Open Source MySQL

An interesting case study from clearmark.com on how the RAD capabilities of MS Access, and the scalability of the open-source MySQL databasse system, can be leveraged to create a low-cost enterprise-level distributed application.

Visual Studio 2005 Tools for Office Beta 2 IntelliSense Code Snippets

Visual Studio 2005 Tools for Office Beta 2 IntelliSense Code Snippets This download contains 71 IntelliSense code snippets for use with Visual Studio 2005 Tools for Office Beta 2

Office 2003 Add-in: Outlook Calendar Views

Office 2003 Add-in: Outlook Calendar Views With the Calendar Views Add-in, you can easily create and manage filters to display subsets of appointments in Outlook Calendar. Filtered views are based on labels and categories that you apply to appointments (7/6/2005).

Office 12 to Make it Easier for Businesses to share Forms

At its three-day partner conference, which starts Friday in Minneapolis, USA, Microsoft is planning its first public demonstration of improved Infopath forms capabilities in Office 12. The Office 12 beta is scheduled for release later this year, and the final version is slated for release in the second half of next year. In the demonstration, an example of a loan application, a potential home buyer fills out a form via the Web. A loan processor is notified via e-mail and can work on the loan document from within the e-mail. InfoPath forms will be also be able to be edited and viewed from within a web browser. Source

The Deal on the New MS Reports Reporting Platform for Office2003

Extending Office 2003 for Enterprise Reporting and Analysis The new MS Reports reporting platform supports multiple Microsoft teams that require reporting by allowing end users to quickly consume and analyze data, using familiar tools and interfaces, without understanding the data sources and systems (6/20/2005).

Visual Studio 2005 Tools for Office Hands-On Labs

These download provide hands-on practice with developing Microsoft Word and Excel solutions using Visual Studio 2005 Tools for Office Beta 2:

• Visual Studio 2005 Tools for Office Beta 2 Hands-on Labs for Word 2003
• Visual Studio 2005 Tools for Office Beta 2 Excel Hands-on Labs

How to Integrate Flash with Office 2003

How to Integrate Flash with Office 2003 Combine the analytical power of Office with the display slickness of Flash.

Managing Access Applications Inside an IT Organization

On Demand MS TechNet Webcast: Managing Access Applications Inside an IT Organization Developers and IT organizations face a multitude of challenges in maintaining a healthy Access database ecosystem. Join this session to review tools and strategies for managing shared database applications in multi-user environment. Also discussed are recommended usage scenarios for Access, and when to consider a move to SQL Server, including options available to help make for a successful migration.

Latest MS Office 2003 Resources

Office 2003 Add-In: International Character Toolbar This add-in enables you to insert international characters from 26 languages into Microsoft Office 2003 programs.

Using the Visual Studio Tools for Office Application Manifest Editor Learn about the application manifest and the process of building the Application Manifest Editor tool, as well as common deployment scenarios supported by this tool.
Creating XML Mappings in Excel 2003 Walk through common XML tasks in Microsoft Office Excel 2003 to learn more about the new XML functionality in Office 2003 Editions.

Microsoft Desktop Deployment Center

The Microsoft Desktop Deployment Center has loads of tools and resources for deploying Microsoft Office Professional Edition 2003:

• Choose and define your Office 2003 editions
• Identify the upgrade issues
• Create the installation point
• Customize Office 2003 editions
• Deploy and maintain Office 2003
• Active Directory and Group Policy
• Office 2003 Resource Kit
• Microsoft Office Access 2003 Conversion Toolkit
• Systems Management Server 2003 (Enterprise Only) Access

What's In Office 12?

What's next for Office? Microsoft VP Jeff Raikes addresses how the company is building solutions for the digital work style as it moves forward with the next version of Office, code-named "O12".

A Better VBA Error-Handler

Today I came across something extremely interesting.

In an article by Mike Groh, editor of Access VBA Editor magazine, excerpted in an on-line teaser at VBA Error-Handling Basics, there is an intriguing piece of VBA code put forward as an example of effective error-handling (the interesting bit is bolded):

Public Sub MyProc()
On Error GoTo HandleError
1000
'A guaranteed error:
2000
Debug.Print 1 / 0
ExitHere:
Exit Sub
HandleError:
Call LogError( _
plngNumber:=Err.Number, _
pstrDescription:=Err.Description, _
pstrModuleName:="ThisModuleName, _
pstrProcedureName:="MyProc", _
plngLineNumber:=Erl)
Resume ExitHere
End Sub

The constant Erl returns the line number in the procedure where the error occurs, but as I discovered after playing around with the code in Access 2000, only if line numbers exist in the procedure. I revised the procedure to:

Public Sub MyProc()
10 On Error GoTo HandleError
'A guaranteed error:
20 Debug.Print 1 / 0

ExitHere:
30 Exit Sub

HandleError:
40 Debug.Print "Error No: " & Err.Number
50 Debug.Print "Error Description: " & Err.Description
60 Debug.Print "Error from Line No: " & Erl
70 Resume ExitHere

End Sub

which returns:

Error No: 11
Error Description: Division by zero
Error from Line No: 20

in the Immediate Window.

So you now have an error-handler that returns the line number in which the error occurred!

Implementing this technique in your VBA applications, new AND existing, is easy using a great freeware Office VBA Editor add-in, MZ-Tools 3.0 VBA , which adds line numbering to a procedure in two mouse-clicks.

Why Switch from VBA Visual Studio Tools for Office 2005 (VSTO)?

Nine Compelling Reasons to Move from VBA to VSTO 2005 from TechRepublic.com. Nine "solid" reasons for giving Visual Studio Tools for Office 2005 a closer look?

Microsoft XML Parser (MSXML) 3.0 Service Pack 5 Now Available

Microsoft XML Parser (MSXML) 3.0 Service Pack 5 (SP5) The Microsoft® XML Parser (MSXML) 3.0 SP5 release offers a number of security fixes and bug fixes over the previous MSXML 3.0 SP releases. You can also download the MSXML 3.0 SDK from this page.

MS Office Interoperability

This article is an excerpt from Microsoft .NET Development for Microsoft Office, from Microsoft Press (ISBN 0-7356-2132-2, copyright Microsoft Press 2005, all rights reserved):

• Chapter 2, Part 1: Discusses managed interop assemblies for Microsoft Office XP and Microsoft Office 2003, and illustrates some basic interop projects with Microsoft Office Excel, Microsoft Office 97, Microsoft Office 2000, Microsoft Office Word, Microsoft Office Outlook, and Microsoft Office PowerPoint.
• Chapter 2, Part 2: Discusses Microsoft Visual Studio .NET Office interop wizards, explains interface/class ambiguity in Microsoft C# and Microsoft Visual Basic .NET, and explains how to release COM objects by using simple garbage collection, the ReleaseCOMObject method, and AppDomain unloading.
• Chapter 2, Part 3: Compares Visual Basic .NET and C#, and illustrates debugging interop solutions. Also includes a summary of the entire chapter.
• Download sample code

Latest MS Office Downloads

Download: Use InfoPath 2003 to give your office applications access to production data An overview of InfoPath's capabilities for better enterprise-wide communication from techrepublic.com.

Create Your Own Symbol Sets for the Office 2003 International Character Toolbar Add-In

Microsoft Component Installer Software Development Kit for Windows (MCISDK)

Latest MS Access On-Demand Webcasts from Microsoft

• Microsoft Office System Webcast: Adding Functionality to Forms with Access 2003
• Small Business Webcast: Customizing Reports Using Microsoft Access 2003

The deal on the new MS Office XML Schemas

Microsoft Office 2003, XML Reference Schemas The deal on the new Office 12 XML file formats. For more details, see the Microsoft Office XML page.

Latest MS Access KB Articles

896906 ACC2000 FIX: You receive an ODBC error message when you run a pass-through query in Office Access to import data from an Oracle database into Office Excel

898789 ACC2002 The Script Editor unexpectedly quits when you use the Script Editor for the first time in an Office program

897764 ACC2002 You may receive a "The Visual Basic for Applications project in the database is corrupt" error message when you try to open an object that is in a compiled database or in a compiled project in Access 2002, in Access 2002 SP1, or in Access 2002 SP2

897764 ACC2002 You may receive a "The Visual Basic for Applications project in the database is corrupt" error message when you try to open an object that is in a compiled database or in a compiled project in Access 2002, in Access 2002 SP1, or in Access 2002 SP2

898058 ACC2003 Description of the Office 2003 post-Service Pack 1 hotfix package: April 28, 2005

898072 ACC2003 Description of the Access 2003 post-Service Pack 1 hotfix package: April 28, 2005

899361 ACC2003 The "Microsoft Office 2003 Service Pack 1 (SP1)" white paper for Microsoft Office 2003 is available

898070 ACC2003 Description of the Microsoft Office 2003 Web Components post-Service Pack 1 hotfix package: April 28, 2005

899414 ACC2003 Description of the Office XP Post-Service Pack 3 hotfix package: May 20, 2005

899600 ACC2003 Text that uses umlaut characters may not correctly import in Office Access 2003

Latest MS Access Resources

Smart Access: How to Tame the Crosstab Missing Column Beast How you can ensure that your data-driven crosstab reports in MS Access can work reliably.

Office 2003 Editions: Access VBA Language Reference This download contains the Microsoft Office Access 2003 Visual Basic for Applications (VBA) Language Reference as a compiled Help file (6/6/2005).

More Articles on Migrating Excel VBA Applications to Visual Studio 2005 Tools for Office

Migrating a VBA Excel Application to Visual Studio 2005 Tools for Office This download contains two versions of an IIS Log Analyzer tool: a VBA version and a Visual Studio 2005 Tools for Office Beta 2 version.

Migrating Excel VBA Solutions to Visual Studio 2005 Tools for Office Convert solutions from Visual Basic for Applications to Visual Studio 2005 Tools for Office and Visual Basic 2005.

Outlook Add-in Support in Visual Studio 2005 Tools for Office

Introducing Outlook Add-in Support for Visual Studio 2005 Tools for Office Outlook add-in support simplifies interaction with the host application and provides a cleaner event framework for creating add-ins along with streamlined security.

Creating an Outlook Task Add-in Solution with Visual Studio 2005 Tools for Office Using new Outlook add-in support in Microsoft Visual Studio 2005 Tools for the Microsoft Office System to create Word 2003 task reports using WordProcessingML and XML expansion packs and a context menu for tasks in Outlook.

Architecture of the Outlook Add-in Support in Visual Studio 2005 Tools for Office Learn about new support for managed add-ins in Microsoft Office Outlook 2003, introduced with Microsoft Visual Studio 2005 Tools for the Microsoft Office System.

Word 2003: Build an App with VB.NET

Word 2003 Sample: Creating a Search Application with Visual Basic .NET Download this sample to create a simple application in Visual Basic .NET that searches a database and uses the results to populate a Word document using XML tags or bookmarks.

Visual Studio Tools for Office: All About Outlook

• Visual Studio 2005 Tools for Office: Outlook (Beta) Create Microsoft Office Outlook® 2003 add-ins using managed code.
• Visual Studio 2005 Tools for Office Sample: Outlook Snippets (Beta) IntelliSense code examples for developing custom add-ins for Microsoft Office Outlook 2003. For more information, see the MSDN article "Introducing Outlook Add-in Support in Visual Studio 2005 Tools for Office" available from the Related Resources section.
• Visual Studio 2005 Tools for Office Sample: Outlook Samples (Beta) Complete code samples for developing custom add-ins for Microsoft Office Outlook 2003.
• Visual Studio 2005 Tools for Office Training: Outlook Hands-on Labs (Beta) Hands-on labs for use in developing solutions for Microsoft Office Outlook 2003.
• Visual Studio 2005 Tools for Office Sample: Outlook Tasks Add-in Sample (Beta) This download accompanies the MSDN article "Creating an Outlook Task Add-in Solution with Visual Studio 2005 Tools for Office" available from the Related Resources section.

Webcast: New features features for Office Developers

MSDN Webcast: Overview of Visual Studio Tools for Office, Version 2005 Reviews new features that enable developers to construct business solutions using Visual Studio 2005 and Microsoft Office.

More On Office 12 and the Open XML File Format

Microsoft Chooses Native XML File Format as Default in Office 12 The Office 12 Open XML will give enterprises the choice of XML or binary format. Is it the beginning of the end for proprietary closed Office file formats?

XML File Format for Office 12 files

Microsoft adding XML file formats to Office 12 XML-based file formats for Excel, PowerPoint and Word in Office 12 are expected to free up data and improve security. The new files will be compatible with existing documents, and a free downloadable "converter" will allows users of Office 2000 and later to work with the new formats. Users will have the option to not use the new files in Office 12, but the XML formats will be set as defaults in the three applications when the package ships, sometime in the second half of 2006.

Create an MS Access/Word Application using VB.NET

Word 2003 Sample: Creating a Search Application with Visual Basic .NET This code sample accompanies the MSDN article "Creating a Search Application with Word 2003 and Visual Basic .NET". The sample application searches an Access database and uses the results to populate a Word document using both XML tags and bookmarks. The article also provides some background on data access technologies in the .NET Framework.

Microsoft Office Knowledge Base RSS Feeds

Microsoft Access 2000
Microsoft Access 2002
Microsoft Access 97
Microsoft Office 2000
Microsoft Office 2003
Microsoft Office 97
Microsoft Office Access 2003
Microsoft Office Access
Microsoft Office XP
Microsoft Office

More Office Downloads

Microsoft Office Live Communications Server 2005 with SP1 Management Pack for MOM 2000 SP1

Microsoft Office Small Business Accounting 2006 Software Development Kit 1.2 Includes documentation and samples - everything developers need to write, build, and deploy Microsoft Small Business Accounting integrated applications.

Cool Tool Tips: A Better Control Tip Paradigm

In Access, the functionality of tools tips has not changed since they were introduced in Access95 ten years ago.

In a free sample Access2000 database Cool Tool Tips, which you can download, I have implemented a more functional and controllable paradigm, which also looks great. If you have an application that is to any degree complex to use, Cool Tool Tips is an easily-implemented solution that avoids the need for a Help file, and keeps tips on the screen for however long they are needed by the user:

When a control with a tip gets the focus, the tip is read from the control's Tag property, and when the control loses the focus the tip is hidden. Clicking on the tip while it is displayed hides the tip.

A tip's heading and text are separated by a "~" delimiter in the string assigned to a control's Tag property. The string is parsed by a generic public function in a module, called by another generic function behind the form.

Latest MS Office Downloads

Network Installation Wizard, Version 2.1 for Office 97 Download the Network Installation Wizard, Version 2.1, for use with the network installation of Microsoft Office 97 (5/24/2005).

System Policy Editor and Office 97 Policy Templates Download the System Policy Editor and Office 97 Policy Templates (5/24/2005).

Microsoft Office Live Communications Server 2003 Hotfix This download contains the hotfix for Microsoft Office Live Communications Server 2003 (5/25/2005).

Word 2003 Sample: Creating a Search Application with Visual Basic .NET Download this sample to create a simple application in Microsoft Visual Basic .NET that searches a database and uses the results to populate a Microsoft Word document using XML tags or bookmarks (5/26/2005).

Resources for Deploying and Automating MS Office

Step by step: Deploy Office 2003 with OneNote 2003 Learn step by step how to deploy Office and OneNote at the same time throughout your organisation.

The Office 2003 Resource Kit If you're a network administrator, you'll want to check out the free Office 2003 Resource Kit. It's the definitive guide to deploying, customizing, and maintaining Office throughout your organisation.

New spreadsheet reveals Office 2003 setup defaults A new spreadsheet, Office 2003 Setup defaults.xls, is now available in the Office 2003 Resource Kit. It documents the default installation settings for Office 2003 and the Office family applications FrontPage, OneNote, Project, and Visio.

Access FrontPage commands programmatically Discover how to access almost every FrontPage command from your VBA code - even commands that aren't available through the FrontPage object model.

Automating the XML data mapping process in Excel 2003 Learn how to use XML maps to customize Excel as a data input and display system.

Migrating to Microsoft Microsoft Office 2003

Microsoft Office XP to Microsoft Office 2003 Migration Issues This document discusses the possible problem areas administrators might encounter when moving from Office XP to Office 2003.

Webcast: Best practices to use when migrating from Office 2000 or Office XP to Office
2003

Migrating Smart Tags from Office XP to the Office 2003 Editions

Microsoft Office 2003 Editions Resource Kit The Office Resource Kit features a collection of tools designed specifically to support the Microsoft Office System, and includes comprehensive discussion of such topics as deployment, security, messaging, and worldwide support.

Fixing a Corrupt MS Access Database

If you suspect your MS Access database is corrupt, try the following fixes in this order:

1. Compact and Repair the Database. It is advisable to create a backup of the database before running Compact and Repair, in the event the operation goes wrong.

2. Check that .mdb compiles without error.

3. Make a backup copy of the original .mdb and use the undocumented /decompile switch to decompile the .mdb, by creating a shortcut to the .mdb on these lines:

"C:\Program Files\Microsoft Office 2003\OFFICE11\msaccess.exe"
"C:\MyDocuments\MyDBase.mdb /decompile

4. Use the undocumented SaveAsText and LoadFromText methods of the Application object.

a. Export all objects, except tables, in the database to text files using the following procedures:

Application.SaveAsText(acQuery, "<ObjectName>", "<Path\File Name>")
Application.SaveAsText(acForm, "<ObjectName>", "<Path\File Name>")
Application.SaveAsText(acReport, "<ObjectName>", "<Path\File Name>")
Application.SaveAsText(acDataAccesPage, "<ObjectName>", "<Path\File Name>")
Application.SaveAsText(acMacro, "<ObjectName>", "<Path\File Name>")
Application.SaveAsText(acModule, "<ObjectName>", "<Path\File Name>")

where <ObjectName> is the name of an existing object, and
<Path\File Name> is the path to and the file name of the text file to be created.

b. Create a new database and import all the database objects into the new database.

• With the new database open, point to Get External Data on the File menu, and then click Import. In the Import dialog box, locate and click on the damaged database, and then click Import. On the Tables tab in the Import Objects dialog box, click Select All. Click OK to import the selected tables.
• Import each of the exported text files to the new database by using the following procedures:
  
  Application.LoadFromText(acQuery, "<ObjectName>", "<Path\File Name>")
  Application.LoadFromText(acForm, "<ObjectName>", "<Path\File Name>")
  Application.LoadFromText(acReport, "<ObjectName>", "<Path\File Name>")
  Application.LoadFromText(acDataAccesPage, "<ObjectName>", "<Path\File Name>")
  Application.LoadFromText(acMacro, "<ObjectName>", "<Path\File Name>")
  Application.LoadFromText(acModule, "<ObjectName>", "<Path\File Name>")
  
  where <ObjectName> is the name of the object, and
  <Path\File Name> is the path to and the file name of the text file to be imported.

For more info about database corruption, refer to:

1. How to Troubleshoot/Repair a Damaged Jet 4.0 Database
2. Microsoft Access Corruption FAQ

The Latest Updates and Ehancements for MS Office

Microsoft To Lock up Office Documents As government gets strict on how companies manage data, Microsoft is looking at ways to make Office files more secure.

Microsoft Offers Peek at next Version of MS Office Slated improvements include enhanced collaboration and individual productivity.

Microsoft Office Live Meeting Office Live Meeting is a new Web Conferencing solution for hosting online meetings, training, and events, which integrates with standard Office applications. A 14 day trial is available.

Microsoft Office Update Check for and download free updates for Microsoft Office.

Resolving References in MS Access Revisited

More on the issue of referencing library databases in Access.

Access will try to update a reference if it cannot find the referenced database in its original location. If the RefLibPaths registry key does not exist or does not contain a proper reference, Access uses the SearchPath API to search for the referenced file.

The following searches are performed in the order indicated:



So for all versions of Access the most reliable approach is to locate the library file in same directory as the MSACCESS.EXE file. You can add additional directory paths to the hierarchy that Access searches by creating a Registry key called RefLibPaths:

\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Access\RefLibPaths

If Access sees this entry, it searches in the specified location before searching elsewhere. Create a string value within this key for each library fro which you want to establish a search path. The value name for the entry is the name of the library database, and the value data is the path to search.

You can bypass the reference model and "hard-wire" the location of a specific library without creating a reference to it by using a Registry key. Create the following key:

\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Access\LoadOnStartup

Enter a value name under this key that is the path and file name for a library database, with the value data set as "RO" if the library is read-only, or "RW" for read/write. you can call procedures in the referenced library using the Run method and the qualified name of the procedure, for example:

Run "MyLib.Mda.MyProcedure"

This approach has limitations however. Because Run cannot return a value and because a library referenced like this through the Registry does not appear in the Object Browser, you cannot directly call a library function in a database with a Registry reference and no reference in the References collection.

Sources:
How Access 2002 resolves VBA references
How Access 2000 resolves VBA references
How Access 97 resolves VBA references
Access 97 Expert Solutions (Que Publishing) by Stan Leszynski

Latest MS Office 2003 Downloads: Fabrikam, Outlook API's, and the Excel XML Toolbox

Office 2003 Tool: FabriKam – The Microsoft Office System Solutions Learning Platform Get a single compiled help file that describes how FabriKam incorporates tools, processes, and technologies that developers can use to build powerful solutions on the Microsoft Office System.

Outlook 2003: Integration API Reference Get reference materials for the Outlook 2003 Integration APIs, including the Account Management API, the Data Degradation Layer API, the Free/Busy API, the MAPI-MIME Conversion API, the Offline State API, the Replication API, and the Store API.

Excel 2003 Tool: Excel XML Toolbox for Microsoft Office Excel 2003 Learn how to use the Excel XML Toolbox to help you work with custom-defined XML schemas, XML and the Excel object model, and SpreadsheetML.

Checking, Resolving, and Fixing Broken Library References in your MS Access Applications

Access versions 97, 2000, and 2002/2003 resolve broken references differently, so you need conditional code for each run-time environment.

The following Access KB articles are a good starting point for this difficult topic:

• How Access 2002 resolves VBA references
• How Access 2000 resolves VBA references
• How Access 97 resolves VBA references

This article from trigeminal.com is the best I have read on the topic: How to guarantee that references will work in your Access Applications

Do-It-Yourself VB Script Center Kit

I have just come across this MS download containing sample scripts and documentation enabling you to create your own version of the TechNet Script Center, either as stand-alone Web pages, stand-alone .vbs files, or as a .chm Help file. The Kit also includes instructions for using these helper scripts, and the really great thing about it is that it contains an MS Access database with 2,214 sample scripts for the Windows Scripting Host!

Latest MS Excel Tools and Resources

Using the Excel XML Toolbox for Microsoft Office Excel 2003 Read this article to learn how to use the Excel XML Toolbox to help you work with custom-defined XML schemas, XML and the Excel object model, and SpreadsheetML.

Understanding Security for Add-ins and VBA Macros in Excel 2003

MS Office Version 12 out in 2006

New Microsoft Office in 2006 11 May 2005 Microsoft will release the next version of Office next year, Bill Gates said yesterday...

Code Sample: Shutdown Word from MS Access

Shut Down an Instance of Microsoft Word From Advisor.com. When an Microsoft Access application that uses Microsoft Word crashes, it doesn't always close the instance of Word. Find out how to shut Word down from within Access.

Excel 2003 XML Toolbox

Excel XML Toolbox for Microsoft Office Excel 2003 Just released and provides many useful tools for working with XML in Excel:

Working with Custom-defined XML Schema

• Reload schema into workbooks while preserving cell mappings.
  
• Build a schema or add to an existing schema, directly from Excel.
  
• View the schema for XML maps stored within workbooks.

Working with XML and the Excel Object Model

• Quickly refresh all XML maps with bound data sources.
  
• Refresh XML maps with bound data sources and persist cell formulas for
  mapped cells.
  
• Use a new XML Range Properties tool to view XML mapped cell properties and
  to copy the XPath or Microsoft Visual Basic for Applications (VBA) statement
  to the Clipboard.
  
• View the exportable XML data without exporting.

Working with SpreadsheetML

• Quickly save the workbook as SpreadsheetML.
  
• View the SpreadsheetML for the active workbook.

Shortcuts and More

• Quickly open and close the XML Source task pane.
  
• Turn on and off the borders for XML mapped cells.
  
• Import and export XML.

Latest Service Packs

Windows SharePoint Services Service Pack 1 Windows SharePoint Services Service Pack 1 provides the latest updates to Windows SharePoint Services.

Microsoft Data Access Components (MDAC) 2.8 SP1 Microsoft Data Access Components (MDAC) 2.8 SP1 contains core Data Access components such as the Microsoft SQL Server™ OLE DB provider and ODBC driver.

New Tool for Viewing MS Access Object Dependencies

I have just launched my latest Add-In and Wizard for MS Access.

MS Access Object Dependency Browser (ODB): the easy way to explore all objects, objet collections, and dependencies of your Access database.

Microsoft introduced a native object dependency viewer in Access2003, where objects can be viewed in separate treeviews: objects that depend on me, and objects that I depend on.

ODB goes much further:

1. Supports Access 97, Access 2000, Access 2002, and Access 2003
   
2. The two dependency views are integrated in the one treeview: for example in the screenshot below, ODB displays not only the fields collection for the Customers table, but linked tables and joins, and all other objects in the database that depend on the Customers table. Clicking on a join field will show the related to table/field and the type of join.
   
3. Full dependency details are also displayed in the adjacent properties pane.
   
4. Each object can be fully documented in a formatted Access report by clicking the Document button.
   
5. Copy of all object details from the properties pane to the Windows clipboard by clicking the Copy to Clipboard button.
   
6. Open database objects in design view with a click of the Design button.

Latest MS Access 2003 Knowledeg Base Articles

Courtesy of kbalertz.com:

• 898469 The Office 2003 Setup program unexpectedly quits, and you do not receive an error message.
• 898512 You receive a "Microsoft has not been installed for the current user" error message when you try to start an Office 2003 program for the first time.
• 896865 You may receive a "The OLE server may not be registered" error message when you try to create a blank data access page in Access 2002 or Office Access 2003.
• 887971 Description of the Access 2003 post-Service Pack 1 hotfix package: November 1, 2004.
• 885259 Description of the French version of the Access 2003 post-Service Pack 1 hotfix package: September 30, 2004.
• 896013 Description of the Microsoft Office Access 2003 post-Service Pack 1 hotfix package: March 26, 2005.

SQL Server 2000 Service Pack 4 Now Available

May 06, 2005 SQL Server 2000 Service Pack 4 (SP4) adds platform support for Microsoft Windows Server 2003 x64 Edition, allowing 32-bit SQL Server 2000 applications to run on 64-bit platforms using the Windows on Windows emulator (WOW64).

SP4 also addresses specific issues discovered in SQL Server 2000 since its ship date and the first service pack to service the 64-bit edition of SQL Server. Because SQL Server service packs are cumulative, SP4 includes all fixes from previously released Service Pack 1 (SP1), Service Pack 2 (SP2), Service Pack 3 (SP3), and Service Pack 3a (SP3a). SP4 can be applied to an original SQL Server 2000 installation or to one where SP1, SP2, SP3, or SP3a was previously applied. This service pack includes the MS03-031 security bulletin released in July, 2003.

Installation of the update to MSDE 2000 is not straightforward, and details are available from the link above.

XML ADO and all that...

Beyond Tables: Dealing with the Convergence of Relational and XML Data Developers are under increasing pressure to work with heterogeneous information. The advent of XML provided a data representation suitable for both regular (database tables) and irregular content, and was a significant step forward, but representation alone is not enough. We need a powerful standardized method for querying that data as well. XQuery fulfills that need (from the DevX.com XML Zone).

ADOMD.NET is a .NET object model, used for building client applications that access an XML for Analysis 1.1 compliant data provider, such as that provided by the XML for Analysis 1.1 SDK.

Microsoft Data Access Components (MDAC) Utility: Component Checker Component Checker utility has been redesigned to provide XML support. The new tool includes updated snapshots for MDAC 2.7 SP1 on Windows XP SP1 and MDAC 2.8 SP2 on Windows Server 2003 SP1.

Outlook APIs, Price-Cut for Visual Studio, and a Better Inputbox

Outlook 2003: Integration API Reference This reference contains conceptual overviews and reference topics to guide you in developing solutions based on the MS Outlook 2003 Integration APIs.

New Pricing for Microsoft Visual Studio 2005 Microsoft has cut the price of Visual Studio to encourage upgrades. Microsoft has announced pricing and licensing terms for Visual Studio 2005, its application development tool set, as well as Microsoft Developer Network (MSDN) subscriptions, the program through which most developers buy Visual Studio.

Smart Access: Access Answers: Hide Your Input, and it's Pretty Common How to enhance the built-in InputBox, as well as how to programmatically call a couple of the standard Windows dialogs (the Color and Font dialogs).