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.