Contact.ContactID = HumanResources.Employee.ContactID" ??
& " WHERE (((HumanResources.Employee.EmployeeID) In" ??
& " (SELECT HumanResources.Employee.ManagerID" ??
& " FROM HumanResources.Employee)));"
Let??™s dissect this SQL statement a bit. Our manager list will show the employee ID as well
as the first and last name for each manager. As you can see, the data is stored in two tables.
The HumanResources.Employee table stores the EmployeeID field and the Person.Contact
table stores the name fields.
The two tables have a common field, ContactID, that is used to join the tables in this
query. Notice the WHERE clause, which contains a SELECT statement within it. This is known
as nested SQL or an SQL subquery. Essentially, it says, ???Only show us those employees
whose employee ID can be found in the result of the subquery that contains only manager
IDs.??? Subqueries such as this are a nice way to avoid creating temporary tables or individual
queries to narrow down our search.
Here??™s the complete GetManagerList code:
Sub GetManagerList()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim xlSheet As Worksheet
Dim sConnString As String
Dim sSQL As String
CHAPTER 2 n DATA IN, DATA OUT 75
Set xlSheet = Sheets("Sheet2")
xlSheet.Activate
Range("A1").Activate
Selection.CurrentRegion.Select
Selection.ClearContents
Range("A1").Select
Set cnn = New ADODB.Connection
sConnString = "Provider=SQLNCLI;Server=MyServerName\SQLEXPRESS;" ??
& "Database=AdventureWorks;Trusted_Connection=yes;"
cnn.
Pages:
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101