Integrating SQL Server & Office 2003

SQL Server is well known for its programmability. You don't need to be a programmer, however, to use the data stored in a SQL Server database. In fact, because Microsoft provides a SQL Server ODBC driver and an OLE DB provider, any application capable of using such middleware can access a SQL Server database. Of the literally hundreds of ODBC- or OLE DB–compliant applications, one of the best known and most widely used is Microsoft Office.

All the major Microsoft Office 2003 applications provide powerful tools that let you incorporate and use data from a SQL Server database. For example, Microsoft Office Word 2003's mail merge capability lets you use contact information stored in your SQL Server database to automatically address and print letters, envelopes, and other items for mailing to customers or employees. Microsoft Office Access 2003 has powerful front-end and report-building capabilities that let you create customized data entry screens and reports without having to master writing code. And you can pair Microsoft Office Excel 2003 PivotTables with SQL Server or Analysis Services to quickly rearrange, summarize, and analyze large amounts of data. It's worthwhile learning how to access your SQL Server database from all three of these Office applications.

Word Mail Merge

Word's Mail Merge function offers a powerful way to combine the text, graphics, and logos supported by Word documents with data from a SQL Server database. To create a basic mail merge using Word and SQL Server, start Word and click Tools, Letters and Mailings, Mail Merge. On the right side of the screen, a task pane offers a selection of document types. Under Select document type, click Letters, then click Next: Starting document. Under Select starting document,choose Use the current document and click Next: Select recipients. Choose Use an existing list and click Browse to display the Select Data Source window. To create a Word data source that connects to SQL Server, double-click +New SQL Server Connection.odc. This action launches the Data Connection Wizard, which lets you enter your SQL Server connection, authentication,and database selection information.

On the Connect to Database Server dialog box, enter the name of your SQL Server system. Under Log on credentials, choose the type of login you want to use. You can either select Use Windows Authentication, or you can select Use the following User Name and Password and type a SQL Server username and password. When you click Next, the wizard displays the Select Database and Table dialog box you see in Figure 1.

As you can see, I've selected the Employees table from the sample Northwind database, which is easier to work with than the newer AdventureWorks sample database.(SQL Server 2005 doesn't include Northwind, but you can download the scripts to install the Northwind and pubs sample databases at http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en.) After you select the desired database and table, click Next to display the Save Data Connection File and Finish dialog box. The connection information is saved by default using the names of the server, database, and table. For example, my data connection was saved as sql2005 Northwind Employees.odc.

Click Finish, then select and sort the columns that you want to include in the mail merge from the Mail Merge Recipients dialog box. After you click OK to include the rows and columns you've chosen, Mail Merge returns you to your Word document. You'll see your selected data source in the Use an existing list section. Click Next: Write your letter to begin composing your letter or memo.

After writing your letter in Word as usual, insert the merge fields where you want them to appear. For instance, click the location in the letter where you want to insert a merged address and choose the Address block link from the Mail Merge Write your letter task pane. A dialog box shows you the address format. Next,click Match Fields to display the Match Fields dialog box shown in Figure 2. The Match Fields dialog box lets you match SQL Server column names to the data elements that will be inserted into your document. As Figure 2 shows, the Match Fields dialog box was able to correctly match most of the columns on the right to the address block fields on the left. You can do the same for a greeting line by selecting the Greeting line link in the Mail Merge task pane. To insert values from other database columns into the text, just position your cursor where you want to insert an item, click the More items link in the Mail Merge task pane, and scroll through the list to select the columns you want.

After matching all the merge fields, click Next: Preview your letters to see how the finished letter will look. All the merged text should appear in the document. If everything looks fine,click Next: Complete the merge, then click Print. After you save the document, you can run the mail merge again in the future by reopening the document, selecting Mail Merge from the Word toolbar, then selecting the Print option.

Access Linked Tables and Database Projects

Access is best known for its standalone database capabilities. But Access can also connect to other relational database platforms, such as SQL Server. Connecting Access to SQL Server lets you take full advantage of Access's application building capabilities, which enable simple yet effective data entry and provide powerful, easy-to-use report writing capabilities. There are two ways you can use Access to connect to SQL Server: by using linked tables or by creating projects.

Linked tables are best suited for situations in which you want to incorporate into an existing Access database information from another relational database external to Access, such as SQL Server. To create a new Access table and link it to SQL Server, open your Access database project and select Tables on the Objects toolbar. Right-click in the Tables pane and select Link Tables from the context menu to display the Link dialog box.

From the Files of Type drop-down menu, select ODBC Databases to display the Select Data Source dialog box. If you've already created an ODBC file or system data source that connects to your SQL Server system, you can select it from the File Data Source or Machine Data Source tab. Otherwise, to create a new file data source, click New on the File Data Source tab to display the Create New Data Source dialog box you see in Figure 3.

Select the SQL Server driver from the list of drivers, then click Next and type the name of the data source. The name is descriptive and can be anything you choose—I used sql2005-northwind-emp. Click Next, then click Finish to display the Create a New Data Source to SQL Server dialog box. The data source name you entered will be displayed. Use the Server drop-down list to select the name of the SQL Server system you want to connect to and click Next. The Create a New Data Source to SQL Server dialog box prompts you for your SQL Server login information. You can either select Windows NT authentication, which will use your Windows logon information to authenticate to SQL Server, or enter a SQL Server login ID and password. After providing all necessaryinformation, click Next.

The Create a New Data Source to SQL Server dialog box lets you select a default database. Select the Change the default database check box, select Northwind from the drop-down list, and click Next. Click Finish to display the ODBC Microsoft SQL Server Setup dialog box. Click Test Data Source to test the data source connection, or click OK to save the data source information.

After creating the data source, Access returns to the Select Data Source dialog box and displays the new data source. Select it and click OK to display the Link Tables dialog box shown in Figure 4. The source of the listed tables is the default database that was selected when the data source was created. Now you can select one or more of the tables to link and click OK. Access creates a linked table in the Tables pane. Linked tables are designated by a green globe icon and can be used much like an Access table. Privileges to the data in linked tables are governed by the rights assigned to the login ID you used to connect the data source to the SQL Server database.

Access projects are best suited for using Access as the front end to a database with the primary storage being located on a database server. Access projects are also a great way to create data entry forms and reports and manage Microsoft SQL Server Desktop Engine (MSDE) and SQL Server 2005 Express Edition databases. To create an Access project, open Access and click File, New to display the New File task pane. Select Project using existing data to display the File New Database dialog box. Name your Access data project—I called mine sql2005- northwind.adp—then click Create to display the Data Link Properties dialog box.

Fill out the Data Link Properties dialog box by selecting the name of your SQL Server system from the Select or enter a server name drop-down list. Then supply the required authentication information either by clicking Use Windows NT Integrated security or by clicking Use a specific user name and password and supplying a SQL Server login ID and password. Use the Select the database on the server dropdown list to choose the target database on the SQL Server system, then click OK. Access retrieves the database object information from SQL Server and displays it in the Access project dialog box, as Figure 5 shows.

From this dialog box, you can access information such as SQL Server tables,which are listed in the Tables pane, and views and stored procedures, which are listed in the Queries pane. Access acts as a front end to SQL Server, letting you query and open the database objects. Any new database objects that you create by using the Access project are created on the SQL Server system.

Excel Data Import and PivotTables

Excel is one of the most widely used front ends to many different data sources,including SQL Server. Often, however, the connection from Excel to data in the database is made by end users,DBAs, or IT professionals using low-tech comma-separated value (CSV) file transfers generated by SQL Server's bulk copy program (Bcp), SQL Server 2000 DTS, or SQL Server 2005 SQL Server Integration Services (SSIS). In many cases, the process of getting database data into Excel can be made faster and more efficient by bypassing the SQL Server file export step and instead using Excel to connect directly to the data. In fact, Excel's database connection methods are very flexible. Two of the most common ways to connect SQL Server with Excel are through direct data imports from relational tables and by using PivotTables to perform data analysis.

To import data directly from a relational table, start Excel and open the spreadsheet into which you want to import the data. Click Data on the toolbar and select Import External Data, Import Data to display the Select Data Source dialog box. You can create a new data source by following the instructions I provided in the Word Mail Merge section, or you can select an existing data source from the list of sources.

After selecting a data source, click Open. The Import Data dialog box lets you select the place within the spreadsheet to insert the imported data; the default location is $A$1 (row 1 column 1). Click OK to copy the data from the Employees table in the Northwind database to the insertion point in the spreadsheet. By default, Excel also displays the External Data toolbar, letting you easily edit the query used to retrieve the data or requery SQL Server for the most recent data changes. Unlike the tables and projects I created in Access, which were linked to SQL Server, Excel creates only a snapshot of the data. There is no live link with SQL Server, and changes you make to the data in the Excel workbook are not made to the base SQL Server tables.

Excel PivotTables are interactive tables that provide a powerful data analysis tool you can use with both relational data and business intelligence (BI) data from SQL Server Analysis Services. PivotTables let you quickly move and compare data, making it easy to identify patterns, trends, and relationships. To connect Excel 2003 to Analysis Services 2000, you can use the SQL Server Analysis Services 8.0 OLE DB provider that's included with Office 2003. To connect to Analysis Services 2005, you need to have MSXML 6.0 and SQL Server 2005 Analysis Services 9.0 OLE DB Provider installed. You can download MSXML 6.0 and Analysis Services 9.0 OLE DB Provider from www.microsoft.com/downloads.

To create an Excel PivotTable report that uses SQL Server data, start Excel and click Data, PivotTable and PivotChart Report to start the PivotTable and PivotChart Wizard. Select External data source for the type of data you want to analyze, choose PivotTable for the type of report, then click Next. Click the Get Data button to display the Choose Data Source dialog box.

Click the Database tab, then select the sql2005-northwind-emp data source you created earlier and click OK. (Although this example uses the SQL Server relational database as a data source, you can also use the OLAP Cubes tab to connect the PivotTable to Analysis Services.) In the Query Wizard - Choose Columns dialog box, scroll through the list of tables and select the Order Details table under Available tables and columns, then click the uppermost arrow pointing to the right to add all the columns from the Order Details table into the Columns in your query box. Click Next to display the Query Wizard Filter Data dialog box. Don't add any filters, but just click Next, then click Next again in the subsequent window. In the Query Wizard - Finish dialog box, select Return Data to Microsoft Office Excel and click Finish to display the PivotTable builder you see in Figure 6.

To build a sample PivotTable, drag the Order ID to the Page Field section of the PivotTable builder. Next, drag the Unit Price and Quantity columns to the Drop Data Items Here section. Finally, drag the Product ID column to the Drop Row Fields Here section. Excel populates the PivotTable as you drag and drop columns into it. You can then use the drop-down list to select specific data values and move columns to different areas to see other possible data relationships. Figure 7 shows a finished PivotTable. In addition to Excel's basic PivotTable functionality, Microsoft provides an Excel addin you can use to build Excel reports based on Analysis Services data. (For more information about this add-in, see the sidebar“Excel Add-in for Analysis Services.”)

Inclusive, Time-Saving Data Access

Access to the information in your database isn't limited to programmers. You can use familiar applications such as Word, Access, and Excel to access database information and use it in a variety of ways. SQL Server's ability to integrate with these Office 2003 applications can eliminate the need for custom programming or the need to build cumbersome multistep flat-file downloads in order to access information from a database. Taking advantage of this capability lets you get to the data you need and can save you time as well.