You can install a copy of the AdventureWorks database by running the file
AdventureWorksDB.msi.
You will be using SQL Server 2005 Management Studio Express to view the various database
objects. To install Management Studio Express, run SQLServer2005_SSMSEE.msi.
1. Open a new workbook and name it DataAccessSample04.xlsm.
2. Before we begin using ADO in Excel 2007, we must add a reference to the ADO 2.8
library (see Figure 2-21).
CHAPTER 2 n DATA IN, DATA OUT 67
a. In the VBE, choose Tools ?¤ References.
b. Select the Microsoft ActiveX Data Objects 2.8 library.
c. Click OK.
Figure 2-21. Adding a reference to the ADO 2.8 library
If you have SQL Server 2005 installed on your machine, you can use that instead of SQL
Server 2005 Management Studio Express.
For our first example, we??™ll be using a parameterized stored procedure to return a list of
Adventure Works employees for a selected manager. We??™ll enter the manager??™s employee ID
and retrieve a list of that manager??™s direct and indirect reports.
The AdventureWorks database contains a stored procedure called uspGetManagerEmployees.
If we expand that item in the Stored Procedures tree, we see that it takes one parameter,
ManagerID, which is of the Integer data type (as shown in Figure 2-22).
Figure 2-22. Parameterized stored procedure in AdventureWorks database, as viewed in SQL
Server 2005 Management Studio Express
CHAPTER 2 n DATA IN, DATA OUT 68
1. In the VBE, add a standard module.
2. Create a new subroutine called GetManagerEmployeeListSQL.
Pages:
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94