Special MOD XP SQL Server Features

If you’re delving into the new SQL Server–related features of Microsoft Access 2002, you know that Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) is one of only two database engines that Access 2002 can use natively. Because MSDE 2000 ships with any Microsoft 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 a lot of sense.

First, MOD XP ships with a royalty-free redistributable license for MSDE 2000. Technically, you can redistribute both versions of MSDE 2000, but the MSDE 2000 license that you get with all other Office XP editions requires that you obtain a new license for each computer on which your MSDE 2000 runs. However, the license that ships with MOD XP transfers a royalty-free right to redistribute MSDE 2000 along with the custom solutions you base on it. This licensing model is the same as the model that Jet-based solutions use except that Jet has a formal runtime component.

The second reason for choosing MOD XP is that it’s the only edition of Office XP that ships with SQL Server 2000 for Microsoft Office XP Developer. Don’t confuse this SQL Server edition with SQL Server 2000 Developer Edition. These editions are distinct and have different feature sets. When you load the CD-ROM for SQL Server 2000 for Microsoft Office XP Developer, the autorun program greets you with the installation interface for SQL Server 2000 Personal Edition—MOD XP bundles SQL Server 2000 Personal Edition with Office XP. So, you get all the benefits of SQL Server 2000 Personal Edition, including SQL Server’s client tools (e.g., Enterprise Manager and Query Analyzer), and you get SQL Server Books Online (BOL), which will help you learn about the advantages that these tools deliver. In addition, you gain Analysis Services, English Query, and the graphical interface for Data Transformation Services (DTS).

The third reason developers might want to opt for MOD XP is that it includes the Workflow Designer, a feature that enables the modeling of workflow processes from Access 2002 through SQL Server or Microsoft Exchange Server. When you use SQL Server, you can choose either SQL Server 7.0 (and Microsoft Data Engine—MSDE) or SQL Server 2000 (and MSDE 2000). Many business requirements, such as processing expense reports and handling requests for customer service, are workflow processes that are easier to model when you use the Workflow Designer. You select the Workflow Designer as an optional feature when you install MOD XP. If you’re going to use the designer with SQL Server, you must designate a specific SQL Server when you install it.

The Workflow Designer relies on two databases to track an application's workflow information and uses a Web site to give users access to the data. The modSystem database maintains basic information (e.g., the database name, the owner, the user directory of authorized users for all workflow applications) for each instance of a workflow application. When you add the Workflow Designer, the MOD XP application automatically sets up the modSystem database on the server you name during installation. A standard SQL Server database that you register for workflow analysis serves as the second database. The registration process adds custom objects for managing the workflow project.

The Workflow Designer uses Access data-access pages to create Web pages based on the SQL Server application database. However, you can build your own custom pages for letting users access the databases. After ensuring the availability of your two databases and Web site, open the Start menu and select the Microsoft Office XP Developer program group, then invoke the Workflow Manager for SQL Server. The MOD XP documentation includes step-by-step instructions for creating databases, registering them as workflow applications, enhancing them, and saving them for reuse as a starting template for future workflow applications.