The Power of Two: Office 2000 and SQL Server 7.0

FEATURE
The Power of Two: Office 2000 & SQL Server 7.0

Microsoft Office 2000 and SQL Server 7.0 are the new dream team for developers who build custom business solutions. Office 2000's new features let developers and power users integrate SQL Server data sources into powerful custom Office applications that you can easily deploy across the office.

Office 2000's new data-access capabilities-the introduction of ADO and the new Access .adp file type-make it nearly as easy for developers to connect with a SQL Server data source as with a Jet data source. The .adp file is an alternative to the .mdb file, the standard Access database file, but .adp links directly to a SQL Server data source through an OLE DB connection. Another Office 2000 innovation that developers can leverage is the Microsoft Data Engine (MSDE), which Microsoft introduced with Office 2000 Standard Edition. MSDE is the desktop version of the SQL Server database engine. An alternative to the Jet database engine, MSDE resembles a SQL Server service in ADO and Access Projects. (SQL Server runs as a Windows NT service on NT computers. MSDE and SQL Server Desktop can run on Windows 9x computers that don't support NT services; both database engines simulate the SQL Server service and two related services.)

ADO in Office 2000
Office 2000 ships with three ADO libraries, ADODB, ADOX, and JRO. These libraries let developers choose the ADO functionality that they will build into an application. Because the ADO libraries don't load automatically, developers can specify the type and amount of ADO functionality they want to make available through their applications.

ADODB and ADOX are generic ADO models that you can use with any database OLE DB provider. You use the ADODB library to develop database connections, perform database commands, and extract recordsets from data sources. The ADOX library enables data-definition and security functions. You can use the data-definition functions to programmatically create tables, procedures, and views with any database that implements the library's feature set. For example, you can use ADOX to add tables to Jet and SQL Server databases. The ADOX security features match the Jet security model. The third object model, JRO, exposes a programmatic interface to Jet-based replication. JRO works exclusively with the Jet database engine.

To use any ADO model in an application, your project must refer to a corresponding library because these libraries aren't built in. Screen 1 shows the References dialog for an example from this article. It includes references to the ADODB, ADOX, and JRO libraries. The ADODB library corresponds to Microsoft ActiveX Data Objects 2.1 Library. (Do not confuse this with Microsoft ActiveX Data Objects Recordset 2.1 Library, which is called ADOR in application code. The ADOR library has a reduced feature set compared with ADODB.) Screen 1 highlights the ADOX library entry. The JRO library entry follows the ADOX library entry. You can select entries in the References dialog to make ADO libraries available for your custom applications.

When you develop applications with Access, you can program the References collection to add an ADO library. Because users can manually add and delete references, your application can fail if it assumes availability of a reference that a user manually removed. Listing 1 demonstrates one approach to programming the References collection. First, the procedure searches the References collection for the ADODB library. Second, if the ADODB library is missing, the procedure adds the library from a file with the AddFromFile method. (Although the file appears to point at the 1.5 version of the ADODB library, it corresponds to the 2.1 version that ships with Office 2000. Microsoft controls the names of these files, and Microsoft retained the old file name, msado15.dll, for a new library, the 2.1 ADODB library.) This procedure works as-is with Access, but other Office components require a reference to the Microsoft Visual Basic for Applications Extensibility Library to run similar code. You need this reference because this library adds the References collection and Reference objects for the other Office components.

SQL Server with Access 2000
Although all Office components support ADO programming, Access 2000 is unique because it also enables a graphical link to remote data sources through Access Projects. This new Access file type, .adp, supports a direct link to a SQL Server service (SQL Server 6.5 and 7.0 and MSDE) via one OLE DB connection. Unlike the traditional links in .mdb files, Access Projects let you create, delete, and maintain databases and their objects. When you open Access, you can choose Project (New Database) to open the Microsoft SQL Server Database Wizard. This wizard creates a new SQL Server database for an Access Project. Using Access Projects with SQL Server offers developers two advantages: Access Projects integrate seamlessly with SQL Server services, and developers benefit from the ease of use and familiarity of the Access database window. You can readily bind forms, reports, and data access pages to tables, views, stored procedures, and ad hoc SQL query statements.

The left panel of Screen 2 shows the Data Link dialog for an Access Project connecting to the NorthwindCS database on the CAB2200 server. (The NorthwindCS database ships with Access 2000 and is a client/server version of the Northwind database that Microsoft shipped with earlier versions of Access. The Northwind database that Microsoft ships with SQL Server 7.0 is similar, but not identical, to the one that ships with Access 2000.) The right panel in Screen 2 presents the Tables collection for the .adp file that links to the NorthwindCS database. You open the Data Link dialog box by choosing the File, Connection command from the Database window menu. You can use this dialog box, which the left panel of Screen 2 shows, to set or reset the OLE DB connection between an Access Project and SQL Server database. Access Projects use the connection to link to the tables, views, database diagrams, and stored procedures in a database maintained by a SQL Server service. Forms, reports, pages, macros, and modules are stored locally in the .adp file for the Access Project.

In SQL Server Enterprise Manager, you can choose Tools, then Database Utilities or Security to expose commands for managing and securing the database associated with an Access Project. For example, you can choose Tools, Security, Database Security to open the SQL Server Login Properties dialog box in Enterprise Manager.

One of the strengths of Access Projects is their ability to integrate traditional Access forms with SQL Server databases. Screen 3 shows a pair of forms from an Access Project. The top form is a switchboard that offers three capabilities. The bottom form is bound to a local data cache based on a remote SQL Server data source. Although Access Project forms bind to a local cache, updates can automatically revise their remote data source. Operations such as finding an employee record perform faster because they operate against a private, local cache.

Listing 2 shows the ADO code that runs when a user clicks the Open frmEmployees button that Screen 3 shows. The code starts with a reference in a Dim statement to the Recordset object in the ADODB library. This reference generates a run-time error unless you either manually or programmatically reference the library. The recordset's Open method sets several essential property settings for a read/write form. You need to designate a keyset cursor type and pessimistic locking because the defaults are forward-only and read-only. These defaults deny write access and back-and-forth record navigation. Setting the cursor's location to adUseClient references the local data cache through the recordset.

Although the ADO commands will be new to you if you're migrating from earlier versions of Access, you'll still find some familiar objects in Access 2000. You can open a form with the OpenForm method for the DoCmd object. Then you can set the form's properties by identifying a member of the Forms collection. A new Recordset property lets you designate a form's record source. This property is richer than the old RecordsetClone property, which supported read-only access to the record source for a form. If your application calls for a read-only form, you can keep the syntax and settings in Listing 2. Set the form's RecordsetType property to 3 for a snapshot source. Then add this line, which can appear after you assign a value to the record source property:

Forms("formname").RecordsetType = 3 (or an integer constant set to 3)

By default, the property is an updateable snapshot (a value of 4) so that changes made to the local cache propagate automatically to the remote data source.

To control the record that the Employee form opens, click the Find An Employee button on either the switchboard form or the data form in Screen 3. Either action invokes the first of the two procedures in Listing 3. The first procedure (callout A) collects the employee ID to display in the form. The second procedure (callout B) opens the form that includes the employee ID, if the ID is in the local data cache.

To refresh the local cache to view changes made by concurrent database users, reopen the recordset for the form. The procedure in Listing 4 shows one approach to this task. The procedure saves the employee ID before invoking openForm from Listing 2.

After openForm returns control, the requeryRemoteRestoreID procedure in Listing 4 searches for the employee ID record as it was before the user called the OpenForm procedure. The procedure repositions the form from the first record to the previously selected record, provided the record is in the refreshed recordset.

SQL Server with Excel 2000
Besides Access, ADO is the main vehicle Office developers can use to tap SQL Server and other remote data sources (e.g., to populate cells on an Excel worksheet). You use a three-step process to access a SQL Server data source programmatically. First, create a connection to the remote data source. Use the Open method for the connection object, as Listing 5 shows. Second, use that connection as the active connection for a recordset. Third, populate an Office object with the field values in the records of a recordset.

If you want to use this strategy for deploying SQL Server data-source values on an Office document, you need to master the ADO object models and the Office object models. The richness of the Office models is a benefit and a bane in this context. The magnitude of what you need to know to leverage that rich functionality can be overwhelming. The online and printed documentation that Microsoft Office 2000 Developer Edition includes is an important resource you can use to master the Office object models. You can also buy the printed version of Microsoft Office 2000 Visual Basic Programmer's Guide from Microsoft Press.

Listing 5 uses ADO programming to populate cells on an Excel worksheet. The procedure in the listing resides in an Excel project that connects to the NorthwindCS database on the CAB2200 server and copies the return set from a stored procedure to a worksheet. Before and after copying the return set to a worksheet, the procedure performs selected formatting functions-entering a title for the table on Sheet1, setting column widths, and setting column alignment-to enhance the interpretation of the return set on the sheet.

The connection string in callout A of Listing 5 has some interesting features. First, it uses the Microsoft OLE DB Provider for SQL Server (SQLOLEDB), which Microsoft optimized for SQL Server 6.5 and 7.0 and MSDE. Alternatively, you could use the Microsoft OLE DB Provider for ODBC (MSDASQL), which targets all ODBC-compliant data sources, including SQL Server. With Office 2000, Microsoft also ships providers for Oracle, Microsoft Index Server, Microsoft Active Directory Service, and Jet 4.0, which ships with Access 2000.

The recordset Open method in callout B of Listing 5 specifies a stored procedure as the source for the records. You don't need to specify the source type for a recordset, but doing so speeds the operation of the recordset's Open method. Other Options settings that you will likely use include adCmdText for SQL strings and adCmdTable to return all the rows from a table. (You can use these in place of adCmdStoredProc in callout B of Listing 5.) Although you don't need to set the Options property, not setting the property causes the Open method to run more slowly.

After you gather information from a recordset, you need to enter it into a worksheet. One easy way to copy recordset field values into a worksheet is to use the Cells property of the Excel Application object to specify a row and column in the active worksheet. After you add values to a worksheet, invoke the AutoFit method to size a column automatically so that it accommodates its widest entry. The example in Listing 5 (callout C) also right-aligns the values in column B by setting the HorizontalAlignment property to xlRight. Finally, after you're finished using a connection that you don't intend to reuse soon, close the Connection object and assign its reference to Nothing to free the resources for the connection.

SQL Server with Word 2000
Every Office component offers unique opportunities and development challenges. The Word 2000 SQL Server example in Listing 6 makes this point by processing the same return set that the Excel example uses. Instead of copying the SQL Server database field values to worksheet cells, this example constructs a series of sentences with the recordset entries. The sentences present the product name and price for each of the 10 most expensive products in the NorthwindCS database.

Word documents offer two locations for storing code. First, you can put your code in the Project for a document. This location is fine for application logic that is specific to an individual document. Second, you can place your code in the Normal template. This location suits application logic that must run in multiple documents. The example in Screen 4 stores code in the ThisDocument folder of the Normal template. You can duplicate this storage method by opening the Project Explorer window in the Visual Basic Editor (VBE) and selecting the ThisDocument folder in the Normal template, as Screen 4 shows. Next, type or copy your code into the code window and click the Save Normal toolbar button to save your code.

Although the Word document uses the same data source as the preceding Excel example, Word lets you format the document on a page with font size settings, bold attribute assignments, and related Word-style property settings. The excerpt in Screen 4 starts by setting the font size for the Selection object. This object represents the portion of a document window that is selected. If the current font is bold, the code toggles it off. Then a For...Next loop applies the TypeText method to copy sentences to the active document with field values from successive records in the SQL Server data source.

Power and Functionality
This article shows you several strategies for tapping SQL Server data sources in Office 2000 applications. These strategies emphasize ADO, but Access 2000 also includes Access Projects. The powerful new file type behind Access Projects speeds performance as it adds new functionality. Access Projects let you create databases, logins, and database objects such as tables, views, stored procedures, and database diagrams. You can also enhance built-in graphical devices with ADO and VBA code.

Office components can readily display SQL Server data in worksheets, documents, and other Office objects, such as PowerPoint slides and Outlook folders.

The SQL Server/Office 2000 combination packs more punch than this article reveals. Office 2000's new features bring unprecedented power and functionality to custom applications that leverage SQL Server data.

SQL Server Magazine
Bugs, Comments, Suggestions Subscribe