What's New in Access 2002

Microsoft Access 2002 is the version of Access that ships with Microsoft Office XP, and as we all know by now, XP stands for experience. When you're using Access projects in Access 2002, you might conclude that Access 2002 is Microsoft's second try to get the experience right—especially for SQL Server 2000. The experience is more friendly, and although Microsoft will undoubtedly continue to enhance Access in future versions, Access 2002 offers a broad range of new features that are likely to be significant for your organization if you're using or planning to migrate to SQL Server 2000.

Microsoft Access 2002 is the version of Access that ships with Microsoft Office XP, and as we all know by now, XP stands for experience. When you're using Access projects in Access 2002, you might conclude that Access 2002 is Microsoft's second try to get the experience right—especially for SQL Server 2000. The experience is more friendly, and although Microsoft will undoubtedly continue to enhance Access in future versions, Access 2002 offers a broad range of new features that are likely to be significant for your organization if you're using or planning to migrate to SQL Server 2000.

Access 2002 is compatible with SQL Server 2000, whereas Access 2000 requires remedial steps to work with SQL Server 2000. And Access 2002 integrates more tightly with SQL Server 2000 than Access 2000 does. In addition, Access 2002 introduces several new and improved visual designers and extended properties that enhance the power and simplicity of working with SQL Server from Access. If you want to start applying XML to accomplish some common database chores easily, Access 2002 introduces two new methods that dramatically simplify importing and exporting XML documents from SQL Server databases.

An Access project is a special kind of Access file that connects through an OLE DB connection to a SQL Server database. An Access project facilitates many SQL Server database definition functions (e.g., the creation of databases, tables, views, and stored procedures) and integrates tightly with Access forms, reports, and Web pages (which Microsoft calls data access pages). Access projects in Access 2000 require a couple of remedial steps for compatibility with SQL Server 2000. (For information about the steps needed for integration, see the Microsoft article "ACC2000: Incompatibility Issues Between Access 2000 Projects and SQL Server 2000" at http://support.microsoft.com/support/kb/articles/q269/8/24.asp.) However, Access 2002 is immediately compatible with SQL Server 2000 (as well as with SQL Server 7.0 and 6.5).

In Access 2002, the UI for the Access project has changed to accommodate new and improved functionality, which makes Access 2002 a better development tool for SQL Server than its predecessor. The UI changes are evident in menu options, the options available from the Database window, and visual designers for tables, stored procedures, and user-defined functions (UDFs).

Transfer or Copy Databases Between SQL Server Instances

Access 2002's Tools, Database Utilities command menu has two new items for copying SQL Server databases from one SQL Server instance to another: Transfer Database and Copy Database File. Both commands copy a database from one SQL Server instance to another, but they have different requirements and techniques. Both commands require the installation of SQL Distributed Management Objects (SQL-DMO) on the workstation that initiates a copy. Despite its name, the Transfer Database command doesn't remove a database from its original location. For a detailed comparison of the Transfer Database and Copy Database File commands, see the topic "About copying data and databases in an Access project" in Access Help.

Using the Transfer Database command is a fast, easy way to transfer all the database objects from one SQL Server instance to another—on the same computer or different computers. The source and destination computers must be running SQL Server 7.0 or later. You don't need a special role on the source instance of SQL Server, but you must be a member of the sysadmin role on the destination server.

Figure 1 shows an Access project's Data Link Properties dialog box. The Access project connects to the NorthwindCS database on the default SQL Server instance on the CABARMADA server. When you select the Transfer Database command, you see the Transfer Database dialog box that Figure 2 shows. This dialog box transfers the NorthwindCS database to the OFFICEDEVELOPER instance of SQL Server on the CAB2000 server. When you click Next, Finish, you launch the transfer of the NorthwindCS database. When the transfer is complete, the Access project remains connected to the NorthwindCS database on the CABARMADA server, and another copy of the database resides on the OFFICEDEVELOPER SQL Server instance on the CAB2000 server.

You can also use the Copy Database File command from the Tools, Database Utilities menu to copy databases between server instances. When you invoke this command, you copy the .mdf file from the database to which an Access project connects to a destination of your choice, which could be another computer or another folder on the same computer. Then you're responsible for attaching the copied .mdf file to another SQL Server instance. You can use T-SQL to attach the .mdf file to a server. For a code sample that you can use to accomplish this task, see sp_attach_single_file_db in SQL Server Books Online (BOL).

Visual Designers

In Access 2000, the Objects bar in the Database window shows four types of server-side objects: tables, views, database diagrams, and stored procedures. The server-side objects that a Database window in Access 2002 shows are tables, queries, and database diagrams. (The new queries object class refers to stored procedures, views, and UDFs.)

The visual table designer in Access 2002 has a new look that experienced Access developers will find familiar. If you're unfamiliar with Access lookup columns, see the "Create a lookup column" topic in Access Help to follow step-by-step instructions for the process. To create a new table, open the designer by selecting Tables on the Objects bar and clicking New. Figure 3 shows the Lookup tab in the visual table designer. On this tab, you can specify relationships for foreign key values so that they show a meaningful string from the referenced table instead of the value for the foreign key. The OrdersWithLookups table uses a five-character field to represent customers. However, if you open the table or base a form on it, the full customer name appears instead of the five-character code. The Lookup tab shows the settings that enable this feature. As Figure 3 shows, the CustomerID column in the OrdersWithLookups table displays the second column from the Customers table. The second column in the Customers table contains customer names.

When you select Queries on the Objects bar and click New in the Database window, the New Query dialog box presents a list of design options. The options in the list vary depending on which release of SQL Server the Access project connects to. When Access 2002 connects to SQL Server 2000, users who have the appropriate permissions can create views, stored procedures, and UDFs. (SQL Server 7.0 doesn't support UDFs.) The New Query dialog box offers some pleasant surprises besides its ability to create UDFs. The new visual designer for stored procedures supports row-returning queries and queries for adding, updating, and deleting records. You can use the graphical query designer to design parameter queries for stored procedures. If you need more capabilities than the visual designer permits (e.g., conditional or looping statements), Access 2002—as earlier Access releases do—still offers a template that helps you write the T-SQL for stored procedures. Double-click Create Text Stored Procedure in the New Query dialog box to open the template.

The New Query dialog box also features the Design In-Line Function visual designer, which supports the in-line type of UDF in SQL Server 2000. This type of UDF returns a table based on one SELECT statement. Access 2002 offers text designers instead of graphical designers for the other two types of UDFs, scalar and multistatement table-valued functions. With these two types of UDFs, you can create functions that return scalar values and tables. You can make UDFs more flexible by using parameters as you do to make stored procedures dynamic and interactive.

Extended Properties

Access 2002 uses its extended property support for SQL Server 2000 to implement lookup columns for SQL Server tables. The extended property feature also supports setting validation rules (called constraints in SQL Server) from the visual table designer and designating subdatasheets. To create a validation rule, right-click a column in a table's Design view and select the Properties item from the context-sensitive menu. Select the Check Constraints tab in the Properties dialog box. You can set the constraint name and enter or edit the expression for the constraint. A Constraint expression text box lets you specify the message that appears when input to the table violates the validation rule. The Check Constraints tab also offers a drop-down list for selecting previously existing constraints. To remove a constraint, click Delete after selecting it.

Database developers can save time formerly spent on simple layout chores when a subdatasheet for a main datasheet replaces a main form or sub form. Subdatasheets work as sub forms do in main-form—sub-form combinations. Database developers, designers, or administrators can use subdatasheets to designate parent-child relationships between record sources. For example, a customer record can serve as the parent for one or more order records. Subdatasheets let you show the records related to a row in a main table. When you specify a subdatasheet, an expand control (represented by a plus sign) appears on the left edge of the record for the datasheet. You can click the plus sign in any record on the main datasheet to display the related records from the subdatasheet. This capability makes viewing and editing values in two related tables easier.

To add a subdatasheet, go to the table's Datasheet view and select Insert, Subdatasheet. Designate the name of the related record source—for example, by choosing a table. Then, from the drop-down list in the Insert Subdatasheet dialog box, specify the fields in the main and sub-record or child-record sources that you want match records on. To drop subdatasheets from a main datasheet, choose Format, Subdatasheet, Remove in a table's Datasheet view. You can also use these techniques to manage subdatasheets for other row-returning database objects (e.g., views).

MSDE 2000

Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) ships with editions of Office XP that also include Access 2002. MSDE 2000 is one of the two database engines that Access 2002 can use natively (the other one is Jet 4.0, which carried over unchanged from Access 2000). The predecessor to MSDE 2000 was the Microsoft Data Engine (MSDE) that shipped initially with Office 2000. MSDE uses the same database file format as SQL Server 7.0 does, whereas MSDE 2000 uses a new database file format associated with SQL Server 2000. The new SQL-DMO object model that ships with SQL Server 2000 installs automatically when you install MSDE 2000. MSDE 2000 supports multiple instancing, a feature introduced in SQL Server 2000.

For a review of MSDE functionality and how it compares with both Jet and SQL Server, see the sidebar "MSDE Primer for Access 2000," January 2000, InstantDoc ID 7720, and SQL Server/Office Integration, "What Is MSDE?" February 2000. Because MSDE 2000 ships as part of Office XP, it requires no Client Access Licenses (CALs). Besides having the same file format for databases as SQL Server 2000, MSDE 2000 shares the same T-SQL syntax and SQL-DMO object model with SQL Server 2000. This compatibility makes upgrading to SQL Server 2000 easy. Some of MSDE 2000's disadvantages are that its performance starts to degrade after five concurrent user connections, it has a maximum database size of 2GB, and it supports only dual processors when running on Windows 2000 or Windows NT. You must evaluate whether MSDE 2000's advantages outweigh these disadvantages for your circumstances.

MSDE 2000 doesn't install as a standard part of Office XP. The Access Help topic "Install and configure SQL Server 2000 Desktop Engine" describes a four-step process for installing MSDE 2000. First, if MSDE is installed, uninstall it. Second, open the MSDE2000 folder on the Office XP CD-ROM or from the network location for Office XP. Third, run the setup.exe file in the MSDE2000 folder. Fourth, restart your computer to complete the installation.

In Win2K and NT, the installation process creates an instance of MSDE 2000 that runs automatically at startup. With other systems (e.g., Windows 98), you need to manually select the autostart service. Open the MSSQLServer icon in the Windows task bar. Make sure that the SQL Server Service Manager dialog box shows SQL Server as its selected service. Click Start/Continue. Before closing the dialog box, select the Auto-start service when OS starts check box.

If you've installed an earlier release of MSDE and you follow this four-step process to install MSDE 2000, you'll orphan any databases associated with the MSDE server. You can copy the database files from the Data folder for MSDE to the new Data folder for MSDE 2000. Next, attach your MSDE databases to the MSDE 2000 server by invoking the sp_attach_db system stored procedure. BOL contains T-SQL code samples that you can adapt to attach the abandoned .mdf and .ldf files. Note that the sample for sp_attach_db is different from the sample you use to attach the .mdf file that the Tools, Database Utilities, Copy Database File command copies. Recovering the old files calls for sp_attach_db instead of sp_attach_single_file_db because your orphaned files probably have log files associated with them.

When you install MSDE 2000 as the Access Help file describes on either a Win2K or NT system, the setup.exe file invokes Windows authentication by default (provided MSDE 2000 runs on a computer supporting that authentication style). If you use a stored procedure from an Access project to reattach your database files to the MSDE 2000 server, you'll have to log in with integrated security as a member of the Windows local Administrators group. (Figure 1 shows the selection of integrated security.) You can run the setup.exe file in the MSDE2000 folder to enable SQL Server security and the sa login with a NULL password. Microsoft recommends that you change the password from NULL immediately after starting MSDE 2000. For details about how to use the switches, see topics 3.1.4 and 3.1.23 in the readme.txt file of the MSDE2000 folder. By using switches, you remove the need to use a Windows login that's a member of the Windows local Administrators group when you attach the abandoned MSDE database files to a new instance of MSDE 2000.

Because MSDE 2000 ships with any Office XP release that includes Access 2002, you might wonder which Office XP edition will work best for your needs. If you're planning to use Access 2002 to build SQL Server 2000 solutions, Microsoft Office XP Developer (MOD XP) makes sense. For the reasons why I recommend MOD XP, see the Web sidebar "Special MOD XP SQL Server Features."

Access 2002 XML Functionality

Access 2002 introduces new features that take advantage of innovations in XML. You can export from Access and import to Access from an XML document by using the World Wide Web Consortium's (W3C's) most recent recommendations. The schema file for the XML document has an .xsd extension. You can also generate an Extensible Stylesheet Language Transformations (XSLT) file to display the XML document in a tabular format on a Web page. The XSLT file has an .xsl extension. Access 2002's new XML processing capabilities apply to Access projects, so you can export and import data from SQL Server databases that include Access projects. Access lets you import and export data manually and programmatically. Although the manual approach makes processing XML data easy, the programmatic capability facilitates transformations that the UI can't accomplish between multiple XML documents and either SQL Server or Jet record sources.

In an Access project, you can save SQL Server tables and views, row-returning stored procedures, and UDFs in an XML document. You can specify for your XML document a target location—such as a Web site—that represents the contents of the database object at the time you exported the object to an XML file. When you export an XML file from an Access project (either manually or programmatically), you can generate as many as four other files. First, the XML document file, which isn't optional, contains an element-centric representation of the values in the database object. Second, you can create a schema with an .xsd file extension, which contains information about the database object's structure. For example, the schema file specifies the primary key of a table and the data types for each column within a table. The third and fourth files are optional and work together. The .xsl file is a transformation file for representing the XML document as an HTML table. The .xsl file transforms the data in the .xml file and represents its output as either an .htm file or an .asp file. You can choose either output file by using the manual or programmatic techniques for creating a file. With both output file types, the original version of the file contains scripting. The difference is that the .htm script runs on the client workstation, but the .asp script runs on the Web server and returns HTML to the browser. Running the script on the server accommodates a greater variety of browser configurations, but this approach puts more work on the Web server. In applications in which the server can become a bottleneck, search for ways to use client-side scripting. For an example that shows how to manually export an XML document, see the Web sidebar "Exporting a Table as an XML Document to a Web Site."

You can also accomplish the exporting and importing of XML documents programmatically. In-depth coverage of this topic is beyond the scope of this article, but Listing 1 and Listing 2 correspond to the steps for the manual examples that "Exporting a Table as an XML Document to a Web Site" describes. Listing 1 shows two procedures for exporting the authors table and generating four files: authors.xml, authors.xsd, authors.xsl, and authors.htm. You should run these two procedures from an Access project connected to the Pubs database. The first procedure, CallExportTitles(), specifies authors as the name of the source database object. Next, the procedure computes three strings that specify the .xml, .xsd, and .xsl URLs. Then, the first procedure passes to the second procedure an intrinsic constant that designates a table as the source for the XML document, the name of the source database object, and the three URLs computed earlier in the procedure. The second procedure, ExportMyXML, specifies these passed values as arguments for the ExportXML method. Note that the method belongs to the Access Application object.

Listing 2 shows two procedures that add a table to an Access project or Access database file that's based on an XML document. The first procedure computes a string value for the URL pointing at the XML document. Next, the first procedure passes the string and an intrinsic constant for creating and populating a table to the second procedure. The second procedure uses two passed arguments to invoke the ImportXML method. These two procedures need to run from an Access database file or an Access project that doesn't have an authors table.

Access 2002 is a significant improvement over earlier releases. New functionality makes Access 2002 a better development tool for SQL Server than its predecessor, and the latest version of Access is compatible with SQL Server 2000 out of the box. Although this article doesn't comment on every new feature, I hope this overview of selected SQL Server—specific features will help you as you investigate whether Access 2002 is right for your organization's needs.