Open sConnString
sSQL = "SELECT HumanResources.Employee.EmployeeID, Person.Contact.FirstName," ??
& " Person.Contact.LastName FROM Person.Contact" ??
& " INNER JOIN HumanResources.Employee" ??
& " ON Person.Contact.ContactID = HumanResources.Employee.ContactID" ??
& " WHERE (((HumanResources.Employee.EmployeeID) In" ??
& " (SELECT HumanResources.Employee.ManagerID" ??
& " FROM HumanResources.Employee)));"
Set rs = New ADODB.Recordset
rs.Open sSQL, cnn, adOpenDynamic
Sheets("Sheet2").Activate
Range("A1").CopyFromRecordset rs
xlSheet.Select
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
Range("A1").Select
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
Set xlSheet = Nothing
End Sub
Run the code, and your result on Sheet2 should look like Figure 2-24.
CHAPTER 2 n DATA IN, DATA OUT 76
Figure 2-24. The manager list displayed
Now that we have our list of managers, let??™s write the code to show the selected manager??™s
staff.
Add a new subroutine to Module1 and name it GetSelectedManagerEmployeeListSQL.
Since this code is very similar to GetManagerEmployeeListSQL, take a look at Listing 2-6,
which shows the entire code set, and we??™ll review the differences.
Listing 2-6. GetSelectedManagerEmployeeListSQL Subroutine
Sub GetSelectedManagerEmployeeListSQL()
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim param As ADODB.Parameter
Dim rs As ADODB.Recordset
Dim xlSheet As Worksheet
Dim sConnString As String
Dim iMgrID As Integer
Dim sMgrName As String
Dim i As Integer
CHAPTER 2 n DATA IN, DATA OUT 77
Set xlSheet = Sheets("Sheet3")
xlSheet.
Pages:
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102