Selecting a manager
In the Macro window, run the GetSelectedManagerEmployeeListSQL subroutine, the results
of which are shown in Figure 2-26.
Figure 2-26. Results of manager??™s employee search
ADO Example 3:Updating SQL Data
Now it??™s time to let Excel 2007 do some real work. We??™ve seen a few different methods of
retrieving data. Let??™s see what we can do to provide some updating capabilities to our worksheets.
In this example, we will import a list of employees with some personal data (???personal
data??? as defined by the AdventureWorks database; I don??™t know that many of us would agree
that this meets our definition). Once we have that list, we??™ll create a routine that lets us update
any information that has changed.
CHAPTER 2 n DATA IN, DATA OUT 80
1. Open a new workbook and name it DataAccessSample05.xlsm.
2. In the VBE, add a new standard module.
3. Create a function named GetEmpList.
4. Add the following code:
Sub GetEmpList()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim xlSheet As Worksheet
Dim sConnString As String
Dim sSQL As String
Dim i As Integer
Set xlSheet = Sheets("Sheet1")
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.Open sConnString
sSQL = "SELECT emp.EmployeeID, Person.Contact.FirstName, " ??
& "Person.
Pages:
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105