Before we begin coding this procedure, note that this stored procedure has five input
parameters. Our earlier GetSelectedManagerEmployeeListSQL procedure called a stored procedure
that took one parameter, which we instantiated and filled, and then appended to a
Command object within the procedure, like so:
Dim param As ADODB.Parameter
'Code omitted...
Set param = New ADODB.Parameter
With param
.Name = "ManagerID"
.Type = adInteger
.Value = iMgrID
End With
With cmd
.CommandType = adCmdStoredProc
.CommandText = "uspGetManagerEmployees"
.Parameters.Append param
End With
We could declare five variables of ADODB.Parameter type and repeat the Set param =...
and the With...End With block five times from within our procedure??”but that would make
the code for this otherwise simple subroutine somewhat lengthy (the coders dictate of keeping
routines to what can be seen on one monitor screen comes into play here). What we can
do instead is use a VBA Collection object that we??™ll fill with Parameter objects (through a
helper function), and that will then be appended to an ADO Command object.
1. OnModule1, create a new subroutine named UpdateEmpPersonalInfo.
2. Add the following variable declarations:
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim colParams As Collection
Dim sConnString As String
Dim i As Integer
3. Insert the following code to activate the data worksheet and set up the Connection and
Command objects:
Sheets("Sheet1").Activate 'make sure we're on the data sheet
Set cnn = New ADODB.
Pages:
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107