XML, SQL Server, and Excel

Recent articles on these MS Office related technologies.

Excel 2003 Sample: Replicating Formulas to SQL Server Learn how to automate a process for storing Excel 2003 formulas in a SQL Server database. This allows access from the database to the calculated spreadsheet values available only in the worksheet.

Automating the XML Data Mapping Process in Excel 2003 In the first of a three-part series, learn how to use XML maps to customize Excel as a data input and display system.

XSH: Interactively Manipulate and Analyze XML Data from DevX.com's XML Zone. Most developers use some kind of XSLT engine to pick out and process data from structured XML files. Learn how XSH, an open source command-line XML shell, lets you interactively query and manipulate this data without the coding overhead.

MS Access Code Samples
A collection of MS Access code samples, with articles, from Microsoft for Access 2003 and previous versions.

Access 2003

Create a PowerPoint Slide Presentation from Scratch Using Access Data Create a slide presentation, as well use an existing PowerPoint presentation inside an Access form.

Use Automation with Access and Outlook Demonstrates using automation to control one program from another program.

Animate Access 2003 Use the Animation ActiveX control, the Timer control, and the Office assistant to animate Access 2003.

Perform Mail-Merges Using XML Data How to use an external XML data file and the Mail Merge feature of Word 2003 to perform a mail merge programmatically from Access 2003.

New XML Features in Access 2003 This download contains several sample files that demonstrate how to import, export, and transform XML data programmatically into Access.

Access 2002

Write Setup Packages for Your Access Databases How to create a custom .msi setup program for your Access database and supporting files.

Program PivotTable Reports in Access 2002 How to programmatically build and manipulate PivotTable reports in Access 2002.

Program OLAP Databases from Access Using DSO How to use the DSO library to create and manipulate OLAP databases and cubes on an Analysis server.

Manage SQL Server Security with Access How VBA projects provide routines to manage SQL Server security settings.

Transform Access XML Files into HTML with XSLT Demonstrates the transformation of Access 2002 XML data into HTML.

Access 2002 Samples These samples demonstrate some of the new features in Access 2002, specifically in Data Access Pages.

Program Printer Settings in Access 2002 How to take a Word document formatted in Outline View and display it as a PowerPoint presentation slide show.

Access XML Import and Export This sample shows how data, schemas, and presentations can be imported to and exported from Access 2002 through the use of XML.

Access 2000

Develop Client/Server Solutions with Access 2000 Projects How to create and work with Access project (.adp) files. The samples accompany a series of articles from the online book, "Developing Client/Server Solutions with Access 2000 Projects," in the MSDN Library. (February 2000)

Access Workflow Designer Call-Tracking Sample Team Solution How to take advantage of the Call Tracking sample team solution. The sample accompanies the article, "Microsoft Access Workflow Designer Call-Tracking Sample Team Solution," in the MSDN Library. (January 2000)

Fundamental Microsoft Jet SQL for Access 2000 Demonstrates the basic mechanics of using Jet SQL to work with data in an Access 2000 database.

Intermediate Microsoft Jet SQL for Access 2000 Sample and article build on the concepts covered in the Fundamental SQL article and gives a much more detailed picture as to what can be accomplished with Microsoft Jet SQL in Access.

Advanced Microsoft Jet SQL for Access 2000 Focuses on the SQL syntax that is most often found in a multi-user environment.

Northwind Traders Direct Sample Application A simple e-commerce application that uses data access pages, frames, and cookies. It was created using Microsoft FrontPage 2000 and Microsoft Access 2000. (January 1999)

Get to Know the Lastest MS Office Technologies

Desktop Deployment—Office 2003 Overview of business value Microsoft gained with the deployment of Microsoft Office Professional Edition 2003.

Using .NET to Boost Microsoft Office Developer Productivity From DevX.com. With Visual Studio Tools for Office, MS Office developers get access to the full power of Visual Studio .NET for building Office applications.

VBA Error Logging

AccessAdvisor.com has just published a timely article on The Value of VBA Error Logging. The article makes the point that any error handling scheme is incomplete unless it includes an error log, and provides some sample code.

My MS Access Add-In, 123 Error-Handler Wizard, can instantly add error-handling and error-logging to your Access databases:

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 to, or you can opt for global error-handling using a global error-handling procedure and an error-log table.
   
3. The Wizard adds error-handling to the selected procedures in one automated operation.

Converting VBA to VB.Net in MS Office

Excellent overview articles on these new technologies:

Convert VBA Code to Visual Basic .NET When Migrating to Visual Studio 2005 Tools for Office Read this article for reasons to migrate your VBA code to Visual Basic .NET and Visual Studio 2005 Tools for Office, a review of the major code conversion issues, and a list of additional resources.

Microsoft Office Information Bridge Framework 1.0 The Microsoft® Office Information Bridge Framework 1.0 enables developers to quickly create and deploy flexible, custom solutions that adapt to an organization’s existing IT infrastructure and require little or no adjustment of back-end systems.

Roadmap to Microsoft Technical Resources and Support

Useful Microsoft links:

Product Solution Center Product support information, including common issues, frequently asked questions, helpful links, tips and how-tos, and latest downloads.

Public Newsgroups Including advice from Microsoft Most Valuable Professionals (MVPs).

Managed Newsgroups MSDN managed newsgroups are available in English to MSDN Universal, Enterprise, Professional and Operating Systems subscribers to receive free technical support on select Microsoft technologies as well as to share ideas with other subscribers.

Support WebCasts Tune in and watch live technical presentations given by Microsoft technology experts, and participate in live Q&A sessions after each presentation.

Community WebCasts Get live and on-demand access to a wide range of technical and business guidance from industry experts.

Community Blogs Browse these Microsoft employee blogs to get insights and opinions about using Microsoft technology and software.

Technical Chats Microsoft provides you a forum to engage in discussions about Microsoft products or technologies in many languages.

Technical Community Sites Find online technical communities for the Microsoft products and technologies you use.

Regional Directors Regional Directors aren't Microsoft employees--they're independent developers, architects, trainers, and other professionals who provide a vital link between Microsoft and the developer community.

ISV Buddy Program Through this 1-on-1 relationship, your Microsoft buddy will be able to guide you to resources and to help you succeed in building solutions on Microsoft platforms.

Latest MS Access KB Articles

Courtesy of kbalertz.com:

895618 OFFXP: An Office XP update does not appear to be installed after you install the update.

895469 ACC2002: Description of the Access 2002 post-Service Pack 3 hotfix package: March 3, 2005

888636 ACC2003 Documenter does not display the DELETE query in Microsoft Access

888696 ACC2003: How to print a check box without borders on a pre-printed form in Access

NATIVE SideBar MS Access Switchboard and GUI Demo
for Microsoft© Access 97 and 2000/2002/2003

My latest Access Demo Database featuring Outlook-style application navigation is now available. A special feature of the Native SideBar Switchboard is that it uses only native Access controls, so it is easy to implement. Just import three objects into your database: a query, a form, and a code module.

VBA vs VB.NET

This week Microsoft announced termination of mainstream support for VB6 at the end of this month, with extended support ending in March 2008. A petition by MVPs has been lodged seeking reversal of this decision, and has also raised the issue of support for VBA.

The current product support life cycle for VBA is:

. Mainstream support ends 31-Dec-08
. Extended support ends 31-Dec-13

so there is some life left in VBA yet.

Also, Bill Gates is on record that VBA will be supported in Office 12 due out in mid-2006, but not after version 12. This should mean that the support life cycle for VBA will move well beyond these dates once Office 12 is released.

This provides some comfort for Office developers like me and, more importantly, users that are still using VBA and avoiding the unwelcome complexities of using the .NET framework.

I consider Microsoft will need to eventually backtrack and continue to support VBA for many years to come for these reasons:

1. The retail versions of Office in use for at least the next decade will directly support only VBA.
   
2. Office as well as a development environment is equally an end-user product, and I don't hear anyone talking about VB.NET macros...
   
3. The current .NET framework push ignores small business users, who don't want or need the complexities that come with VB.NET.

Documenter does not display the DELETE query in Microsoft Access

Microsoft recently issued a Knowledge Base Article on a documentation bug in Access versions 2000 and later: 888636 Documenter does not display the DELETE query in Microsoft Access.

The native Access documenter does not document DELETE queries in a database. Certain workarounds are suggested by MS, or you can use my Ezy Documenter Access Add-In to seamlessly document all objects in your Access database, including DELETE queries:



There are two versions:

• Ezy Documenter standard is free to buyers of any product from aadconsulting.com or for a one-off subscription fee of US$9.95, which also gives you access to 18 other Access tools.
  
• Ezy Documenter Premium allows user customization of each Ezy Documenter database object report and costs only US$29.95

I wish I could do this in MS Access...

There are things that Access users and developers often do in Access that could be a lot simpler or that they can't do at all. Over the years, I have developed some tools for Access versions 97 to 2003 to make up for some missing functionality. The following tools are available from my web site at aadconsulting.com:

1. Ezy Debug A frustration in debugging in Access 2000 and later is the clunky rigmarole required to turn error-trapping on and off. My free tool Ezy Debug is launched from the right-click popup menu in the code window of the Access VB Editor. The applet provides quick access to the Access error-trapping settings in the code window, and once invoked it is available both in the Access Database and VBE window. The current error-trapping setting is displayed and can be changed on the fly...
   
2. Ezy Access Launcher If you need to support multiple versions of Access, you know that opening an .mdb file in the correct version of Access in Windows Explorer is not straight-forward. My free tool Ezy Access Launcher automatically opens Access files in the appropriate version, and you can easily create Access shortcuts containing the full range of Access command-line switches.
   
3. Find Databases Wizard Want to to locate and document the location of all Access files in a particular location? My free applet, the Find Databases Wizard finds all the MS Access files in the location you select and classifies each file by Access version, lists all the files found in the location, and saves the data in a text-delimited file.
   
4. Ezy Exporter Wouldn't it be great if you could export Access objects in bulk in the same way Access natively allows you to bulk import objects into a database? My Access tool, Ezy Exporter allows you to do just that.
   
5. Ezy SQL Editor and Builder is a sophisticated tools for handling SQL in Access, but it also has some basic features that makes working with queries a whole lot easier by providing a Font... option when editing SQL strings, and adding find/replace functionality.

Latest for MS Office Developers

Customizing List Item Forms in Windows SharePoint Services Enhance and extend interaction with lists in Microsoft Windows SharePoint Services when customizing forms used to work with list items.

Accessing FrontPage Commands Using VBA Discover how to access almost every FrontPage command from your VBA code—even commands that aren't available through the FrontPage object model.

Creating Excel Solutions for Use in Multiple Countries/Regions Using Visual Studio Tools for Office Learn which issues you should consider when using Visual Studio Tools for Office to create Excel 2003 solutions for deployment to end users in more than one country or region.

Enabling SSL Protection on MCMS Sites Learn how to set up Secure Sockets Layer (SSL) for your Microsoft Content Management Server (MCMS) 2002 site.

Latest MS Access KB Articles

Courtesy of kbalertz.com:

889520 ACC2002 When you paste an Excel chart that contains an embedded arrowhead to a Word document, the appearance of the arrowhead changes in Office XP and in Office 2003

892490 ACC2002 How to create a DSN-less connection to SQL Server for linked tables in Access 2003 and in Access 2002

892608 ACC2003 You may receive the "Syntax error in INSERT INTO statement" error message when you use ADO.NET code to access Office Access 2003

887035 ACC2003 Your XML data may appear to incorrectly import when you try to import that data to Microsoft Office Access 2003

889186 ACC2003 Description of the Access 2003 post-Service Pack 1 hotfix package: November 12, 2004

889490 ACC2003 Contents of the Memo field may appear as control characters when you use the GROUP BY clause on the Memo field in a query in Microsoft Access

873334 ACC2003 Description of the Italian version of Access 2003 post-Service Pack 1 hotfix package: December 17, 2004

Latest Resources for MS Office

How Excel 2003 Infers XSDs When Importing XML Review the rules and instances where Excel 2003 infers schema from XML data during import and export.

Customizing Office 2003 Research Services: 15 Answers to Common Questions Common issues facing developers when customizing the Office 2003 Research Services including tips about formatting data, scaling images, and encoding data.

Customizing Microsoft Office Information Bridge Framework for Microsoft CRM Learn to customize the Microsoft Office Information Bridge Framework for Microsoft Business Solutions CRM to meet the unique needs of your business organization.

Visual Studio .NET Developer: Introducing a New Data Grid The .NET 2.0 DataGridView supersedes the .NET 1.x DataGrid control. The DataGridView supports extensive customization and fine-grained formatting, flexible sizing and selection, better performance, and a richer event model.