Silver Collection > Jun-2001 > Access 2002/XP for Developers

200106_mg1 Mike Gunderloy Silver Collection

Microsoft's marketing spin for Office XP seems to be "easier than ever to use." While taskpanes and the demise of Clippit might be interesting to new users, they're hardly compelling features for serious developers. In this article, Mike Gunderloy digs into the guts of Access 2002 to find out what it has to offer for people who are serious about coding.

ad2468x60 <<< Change versions of Access Easily

If you've been getting e-mail from Microsoft lately, you've probably been treated to the somewhat hideous spectacle of www.microsoft.com/office/clippy. In that demo, Microsoft's marketing team brags that Office XP is so easy to use that the Office Assistant is obsolete. As far as I can tell, there's nothing on that site that speaks to developers like you and me at all. Fortunately for Access developers, Office isn't just about taskpanes and HotMail integration; there are serious developer feature improvements in the new version of Access. In this article I'll, try to let you know what's going to make your life easier.

A note on terminology: In case you hadn't noticed, the Office suite is now called "Office XP." However, the members of the suite are Access 2002, Word 2002, Excel 2002, and so on. Internally, the release version for Access is 10.2627.2625, so referring to the product as "Access 10" is probably okay, too.

PivotTables and PivotCharts

There are the expected changes in Access's user interface, as you might expect. However, to developers the appearance of the buttons and menus isn't any more significant than the fins on a '57 Chevy. I'll start with the one big flashy user interface improvement that does matter: PivotTable and PivotChart views of forms. Figure 1 shows these two new views as displayed by the Sales Analysis form in the Northwind sample database.

200106_mg1 Figure 1

The PivotTable and PivotChart views of a form provide the same live interactive analysis features that you might already be used to from Excel or the Office Web Components (OWC). In fact, these views are the Office Web Components. The Access team did the work to hook Web components up automatically, including merging the OWC events and properties so that they display on the form's property sheet, automatically binding the components to the form's recordsource.

The PivotTable and PivotChart views are readily available from the View menu or the form's shortcut menu, allowing users to invoke them whenever they want to slice and dice the recordsource's data. Of course, this can pose a problem if you don't want users performing ad-hoc queries against large data sets. Fortunately, Access has new AllowPivotTableView and AllowPivotChartView properties that allow you to selectively suppress these views for individual forms. You also have complete access to the rich event and object models of the PivotTable and PivotChart components, allowing you to track user interactions with these views if you wish. The OWC programming model has itself been significantly extended in Office XP, so you can exercise even more control over these tools programmatically.

And the DoCmd.OpenForm method now accepts acFormPivotChart and acFormPivotTable constants to let you open forms directly in these views.

Here is an article written later in Smart Access on Pivot Charts Programming Pivot Tables for Access Forms

New events

You'll find a bunch of new events for Access 2002 forms, as Figure 2 shows. But before you get too excited about these events, you need to realize that only the Undo and MouseWheel events fire in regular form view. The rest of the new events are the PivotTable and PivotChart events from the OWC. Even events on this list that would make sense in form view—such as BeforeScreenTip, which lets you edit ToolTips on the fly—are completely unavailable in form view. Oh, well—maybe next time.

200106_mg2 Figure 2

The MouseWheel event is interesting in theory, but in practice it suffers from implementation. Here's the declaration for the event:

Private Sub Form_MouseWheel( _

ByVal Page As Boolean, ByVal Count As Long)

Notice what's missing? There's no Cancel argument. You can sense that the user is changing records on a form via the mouse wheel, but you can't do anything about it. In addition, given that you're going to receive a Current event as well, it's hard to come up with a good use for the MouseWheel event.

The new Undo event, on the other hand, can be quite useful. Available for forms, text boxes, and combo boxes, this event fires when the user types Esc or Ctrl-Z, or selects the Undo item on the Edit menu. This event comes in handy when you want to reverse the side effects of some change that Access won't automatically reverse itself. For example, you might use the Undo event of a combo box to remove lookup records that were added by a NotInList event tied to the same combo box. Or, for an even simpler example, you can add a warning when the user is about to undo all of his or her changes on a form by using code like this:

Private Sub Form_Undo(Cancel As Integer)

Dim intRet As Integer

intRet = MsgBox( _

"Do you really want to discard your changes?", _

vbYesNo, "Undo warning")

Cancel = (intRet = vbNo)

End Sub

Data Access Pages gain a bunch of additional events in Access 2002. Implemented by the MSODSC object, these include AfterDelete, AfterInsert, AfterUpdate, BeforeDelete, BeforeInsert, BeforeUpdate, Dirty, Focus, RecordExit, and Undo. With these events available, it's suddenly possible to make bound DAPs play well in an Access application.

Designing objects

Although the Access design views are pretty mature at this point, the developers in Redmond still found a few things to add. Perhaps the most significant of these is multiple-level undo and redo. You can now undo up to 20 actions in the design view of an object. There are some limitations (for instance, no undo on table design changes in an Access project), but overall this change is a welcome and often-requested enhancement.

Subform and subreport design is also much improved in Access 2002 from Access 2000. For starters, the scrollbars for a subform or subreport in design view now scroll in increments small enough to actually be useful, making it possible to edit subforms/reports in place without making you want to throw the keyboard through the monitor. Even better, the shortcut menu for a subform in design view now includes "Subform in New Window," which will launch a second instance of the form designer to display the subform. Once you've opened a subform in a new window, you'll need to close and reopen or switch views on the main form if you want to edit it in place again, but this is much less of an annoyance than the impossible-to-edit subforms of Access 2000.

The Data Access Page designer has undergone a tremendous round of improvements in this release. Here are a few of the welcome changes:

•If you drop a lookup field on a DAP, it actually shows up as a combo box rather than a text box.

•There's support for relative paths, so DAPs don't break when you move them along with the database.

•There are much more understandable visual cues when dropping related tables to build banded pages.

In addition, standard forms and reports in design view now have a new choice on the Save As menu item: You can save ordinary Access forms and reports as Data Access Pages. The conversion works very well, and it might mean that you don't have to deal with the Data Access Page designer at all.

Miscellaneous changes

Is anyone surprised that Access 2002 introduces yet another Access file format? The good news is that Access 2002 is a lot friendlier to other file formats than previous versions of Access were. You can work with Access 2000 databases directly in Access 2002, and convert back and forth between Access 97, Access 2000, and Access 2002 formats.

As you might expect, you can run into trouble converting backwards. For example, if you use the new Printers collection in Access 2002 and then convert the file to Access 2000 format, you'll get a compile error in Access 2000. But by and large the conversion works well. When it doesn't work, Access automatically builds a Conversion Errors table that you can inspect for hints as to what went wrong.

By the way, Microsoft claims that it won't need to change the file format again to accommodate future versions of Access. We'll see.

And yes, you read that right. The Access object model now includes a Printers collection. I'll tell you a bit more about that in the next section of this article.

Object model changes

Of course, what would a new version of Access be without enhancements to the object model? There are quite a few in this version; I'll just mention the ones that I think are most significant to developers.

First, there's the Printers collection and the Printer object. With these, you can do things like set the default printer or change paper size or margins without needing to manipulate arcane properties like PrtDevMode.

ComboBoxes and ListBoxes now have AddItem and RemoveItem methods so that, in some circumstances, you can avoid writing callback functions to fill list boxes. However, these methods operate on a value list, so they're subject to the length restrictions of value lists. Fortunately, that restriction has been relaxed in this version, and a value list can now contain up to 32,000 characters.

Application.ImportXML and Application.ExportXML increase the interoperability of Access with other XML-enabled applications. The Application object also has a BrokenReference property that lets you check quickly whether there are any broken references, without needing to loop through all of the references in the project.

The AccessObject object now supports DateCreated and DateModified properties. These will mainly be of interest to those who are writing tools to manipulate Access objects.

The Access 2002 report object has been enhanced with new properties that minimize the differences between forms and reports. The new properties include Modal, Popup, BorderStyle, AutoResize, AutoCenter, MinMaxButtons, CloseButton, and ControlBox. Best of all, though, reports now have an OpenArgs property. No longer do you need to use the kludge of opening a form just to pass runtime information to a report.

Forms and reports both get a Moveable property in Access 2002, as well.

XML support

It should come as no surprise to anyone that better XML support is a major thrust of Access 2002. Whatever you might think of XML, it's certainly become a major part of Microsoft's standards for the future.

For starters, you can export tables, queries, and the data behind forms and reports to XML files. As part of the export process, you can choose whether to create an XSD file containing schema information as well as an XML file containing the data. You can also specify an XSL file to control the presentation of the data.

As a simple example, here are the results of exporting the Shippers table from the Northwind sample database. First, the Shippers.xml file contains the data:

<?xml version="1.0" encoding="UTF-8"?>

<dataroot

xmlns:od="urn:schemas-microsoft-com:officedata"

xmlns:xsi=

"http://www.w3.org/2000/10/XMLSchema-instance"

xsi:noNamespaceSchemaLocation="Shippers.xsd">

<CompanyName>Speedy Express</CompanyName>

</Shippers>

<CompanyName>United Package</CompanyName>

</Shippers>

<CompanyName>Federal Shipping</CompanyName>

</Shippers>

</dataroot>

And here's the XSD file that contains the schema description of the XML file generated from the table:

<?xml version="1.0" encoding="UTF-8"?>

<xsd:schema

xmlns:xsd="http://www.w3.org/2000/10/XMLSchema"

xmlns:od="urn:schemas-microsoft-com:officedata">

<xsd:element name="dataroot">

<xsd:complexType>

<xsd:choice maxOccurs="unbounded">

<xsd:element ref="Shippers"/>

</xsd:choice>

</xsd:complexType>

</xsd:element>

<xsd:element name="Shippers">

<xsd:annotation>

<xsd:appinfo>

<od:index index-name="PrimaryKey"

index-key="ShipperID "

primary="yes" unique="yes" clustered="no"/>

</xsd:appinfo>

</xsd:annotation>

<xsd:complexType>

<xsd:sequence>

<xsd:element name="ShipperID"

od:jetType="autonumber" od:sqlSType="int"

od:autoUnique="yes" od:nonNullable="yes">

<xsd:simpleType>

<xsd:restriction base="xsd:integer"/>

</xsd:simpleType>

</xsd:element>

<xsd:element name="CompanyName"

od:jetType="text" od:sqlSType="nvarchar"

od:nonNullable="yes">

<xsd:simpleType>

<xsd:restriction base="xsd:string">

<xsd:maxLength value="40"/>

</xsd:restriction>

</xsd:simpleType>

</xsd:element>

<xsd:element name="Phone" minOccurs="0"

od:jetType="text" od:sqlSType="nvarchar">

<xsd:simpleType>

<xsd:restriction base="xsd:string">

<xsd:maxLength value="24"/>

</xsd:restriction>

</xsd:simpleType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:schema>

Of course, Access can also import XML files, and it can use associated XSD files to make sure that the data comes in with the proper schema. In fact, Access 2002 can import most XML files, but if you don't have a corresponding XSD file the results are likely to be poor.

There's room for improvement on the XML front. One obvious problem is that you can import but not link XML data sources, thus making it impossible to use XML data as a live feed. But I suspect this is an area that's being heavily worked on for an upcoming Office.NET release.

Why not read these articles that were written later on Access 2003 and XML or Put XML to Use in Access 2000+

Access projects

Access projects (Access user interface on the SQL Server database engine) come in for major upgrades in this version as well. To begin with, ADPs can now use the SQL Server 2000 database engine, and the redistributable version of that engine comes along with Access 2002. If you're using ADPs, you should definitely upgrade, because the combination of Access 2002 and SQL Server 2000 finally allows the use of extended properties in Access projects.

Why is that significant? Because extended properties are the key to much of the Access user interface. For example, try resizing a column in a datasheet in an Access 2000 ADP. When you close and reopen the datasheet, your changes to the display of the table will be lost. Now try the same thing in an Access 2002 ADP; you'll find that your changes persist. Extended properties are behind many of the user interface tricks that make Access easy to work with, and having them available in an Access project is a welcome relief. Subdatasheets and lookup properties for fields are just two more of the many features that this simple change enables. If you're working with ADPs, this change alone can make the upgrade compelling.

Another new feature, the Link Table Wizard, makes working with heterogeneous data sources in Access projects much simpler. Figure 3 shows the Link Table Wizard in action.

200106_mg3 Figure 3

The Link Table Wizard works by constructing SQL statements to build linked servers and views on the server side. This is, at least in theory, something that you could do yourself with Access 2000. But it's one of those tedious tasks that's made much easier by having a Wizard handy.

Gone is the distinction between views and stored procedures in the database container. Instead, there's a Queries tab, which contains views, stored procedures, and functions (functions are SQL Server 2000 objects that allow you to construct the equivalent of function procedures in Transact SQL). Another big improvement here is a graphical designer for stored procedures, so that you can build stored procedures even if your knowledge of SQL Server's T-SQL language is rudimentary.

Alas, in some other areas you'll need to know more about SQL Server than you did in Access 2000. All of the menu items and dialog boxes to deal with SQL Server 2000 replication and security are gone from the Access 2002 version. To use that functionality, you'll have to go into SQL Server 2000 itself. To partially make up for their loss, there are several new choices that make it very easy for you to copy SQL Server databases to a new computer.

Unfortunately, it doesn't get any easier to deploy an ADP in Access 2002. If you purchase Microsoft Office XP Developer, you'll get a Packaging Wizard with an overhauled user interface, but putting all of the pieces together is still as hard as ever.

Try this article on Using Stored Procedures in ADPs

Making the (up)grade

As you can see, despite the fact that some pundits are already pronouncing Office XP a minor upgrade, there are quite a few new features for the hard-core developer. Here's my Top 10 list of suggestions for those who are looking to migrate:

•If you upgrade to Access 2002, stick with the Access 2000 database format until you need a feature that doesn't work in that format. Access 2002 does a good job of working with Access 2000 files, which means you can continue to work easily with customers who don't migrate as quickly as you do. This provides a low-risk way to get to know the new version.

•Evaluate XML as a data interchange format anywhere that you're swapping data periodically with another application, particularly if the swap is over the Internet. You might find yourself writing additional code to plug holes in Access 2002 or the other application, but over the long run this is clearly an area that will see better and better support.

•Look for places in your applications that use crosstab queries. These can almost certainly be replaced by PivotTable or PivotChart views of the data with little effort, and your application will be a lot more flexible (and flashier!) as a result.

•While you're looking at your application, see whether you're using list-filling callback functions for small amounts of data. If so, you'll find the code much easier to understand and maintain if you switch to using the AddItem method.

•The writing was on the wall for DAO with the last version of Access. This time, that wall is getting ready to fall on your head. If you haven't converted to ADO already, you need to bite the bullet and get started. We've reached the point where DAO has very little chance of ever picking up new functionality.

•If there are excess forms in your application that exist only to supply report parameters, it's time to replace them with use of the new Report.OpenArgs property.

•If you considered and rejected Data Access Pages in Access 2000, take another look. The improvements to the designer and the DAP event model, as well as the ability of DAPs to work with relative paths, make this a much better technology for the delivery of live data on an intranet in the new version.

•Along the same lines, take a look at your application to see whether it makes sense as an Access Data Project using the SQL Server 2000 database engine, rather than an Access database on the Jet engine. The SQL Server engine's better reliability might make sense for critical data. Many of the annoyances of working with ADPs are gone thanks to extended properties.

•Code that uses the PrtDevMode and PrtMip properties can, in some cases, be easily replaced by using the new Printer objects. Once again, less code makes for easier maintenance.

•If you've been working around any painful bugs in Access 2000, take a look at Access 2002 to see whether they're fixed. The "What's New" lists you'll find here and elsewhere can only cover the highlights. Microsoft routinely fixes thousands of minor bugs with every version, so it's possible that you'll be able to get rid of workarounds.

So, should you be moving to Access 2002? This depends somewhat on which version of Access you're currently using. If you never moved beyond Access 97, it's definitely time to upgrade. That's in part because of the accumulated new features across two versions of Access, but mainly because Microsoft will be dropping Access 97 support as part of its normal policy for phasing out old versions of software.

The bottom line for Access 2002 from the Access 2000 developer's point of view is somewhat more complex. There's no single compelling feature to force you to upgrade. Just about everything that you can do in Access 2002 you can also do in Access 2000. But, for the first time, there's no drawback to upgrading either, because you're not forced to go through a complete file format migration for every database that you're developing. That's enough to tip the balance for me to installing and using the new version. Being able to make a gradual transition means that you can introduce new features to applications at your clients' pace while learning them at your own pace—a good deal, in my book.