Contact.LastName, emp.NationalIDNumber, " ??
& "emp.BirthDate, emp.MaritalStatus, emp.Gender " ??
& "FROM HumanResources.Employee AS emp " ??
& "INNER JOIN Person.Contact ON emp.ContactID = " ??
& "Person.Contact.ContactID"
Set rs = New ADODB.Recordset
rs.Open sSQL, cnn, adOpenDynamic
For i = 1 To rs.Fields.Count
ActiveSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
Next i
xlSheet.Range(xlSheet.Cells(1, 1), _
xlSheet.Cells(1, rs.Fields.Count)).Font.Bold = True
ActiveSheet.Range("A2").CopyFromRecordset rs
CHAPTER 2 n DATA IN, DATA OUT 81
xlSheet.Select
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
Range("A1").Select
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub
This should be fairly standard code by now. We??™re setting up our worksheet, opening
our ADO Connection object, filling a recordset with employee personal data from our
SQL statement, and then displaying it on the worksheet.
nNote All of our examples require a reference to the Microsoft ActiveX Data Objects 2.8 library.
5. Run the code and show the employee personal information data (see Figure 2-27).
Figure 2-27. Employee personal data list
The AdventureWorks database comes with a stored procedure called HumanResources.
uspUpdateEmployeePersonalInfo that will update this information (see Figure 2-28).
Figure 2-28. uspUpdateEmployeePersonalInfo and parameters
CHAPTER 2 n DATA IN, DATA OUT 82
We are going to write a procedure called UpdateEmpPersonalInfo that will call this stored
procedure and update the database with the information from the currently selected row in
our Excel worksheet.
Pages:
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106