ADO Example 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.

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.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 xlSheet.Select

Range( "A1").Select

Selection.CurrentRegion.Select

Selection.Columns.AutoFit

Range(

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.

Note 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).

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).

El Ik] HumanResources.uspUpdateEmployeePersonalInfo El EL Parameters fiip @EmployeeID (int, Input, No default)

ftjp @NatlonalIDNumber (nvarchatdS), Input, No default)

fcip @EilrthDate (datetime, Input, No default)

fcip @MarltalStatus (nchatd), Input, No default)

fiip @Gender (nchatd), Input, No default)

[j^j Returns Integer

Figure 2-28. uspUpdateEmployeePersonalInfo and parameters

We are going to write a procedure called UpdateEmpPersonallnfo that will call this stored procedure and update the database with the information from the currently selected row in our Excel worksheet.

Before we begin coding this procedure, note that this stored procedure has five input parameters. Our earlier GetSelectedManagerEmployeeListSOL 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 = "ManagerlD" .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. OnModulel, 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.Connection sConnString = "Provider=SOLNCLI;Server=MYSERVERNAME\SOLEXPRESS;" * & "Database=AdventureWorks;Trusted_Connection=yes;"

cnn.Open sConnString

Set cmd = New ADODB.Command cmd.ActiveConnection = cnn

4. Next, fill the colParams collection with ADODB.Parameter objects:

Set colParams = SetParams(ActiveCell.Row)

The SetParams function returns a filled collection and looks like this:

Function SetParams(RowNum As Integer) As Collection 'returns a collection of filled ADO Parameter objects Dim colReturn As Collection Dim prm As ADODB.Parameter

Set colReturn = New Collection Set prm = New ADODB.Parameter With prm

.Name = "EmployeelD" .Type = adInteger .Value = Cells(RowNum, l).Value End With colReturn.Add prm

Set prm = New ADODB.Parameter 'wipe prm and start over; best way to prevent leftover data

With prm

.Name = "NationallDNumber" .Type = adLongVarWChar .Size = l5

.Value = Cells(RowNum, 4).Value End With colReturn.Add prm

Set prm = New ADODB.Parameter With prm

.Name = "BirthDate" .Type = adDBTimeStamp .Value = Cells(RowNum, 5).Value End With colReturn.Add prm

Set prm = New ADODB.Parameter With prm

.Name = "MaritalStatus" .Type = adWChar .Size = 1

.Value = Cells(RowNum, 6).Value End With colReturn.Add prm

Set prm = New ADODB.Parameter With prm

.Name = "Gender" .Type = adWChar .Size = 1

.Value = Cells(RowNum, 7).Value End With colReturn.Add prm

Set prm = Nothing

Set SetParams = colReturn End Function

There is nothing really fancy going here, although we have called upon a new property of the Parameter object. We're instantiating the Parameter object with this line of code:

Set prm = New ADODB.Parameter

Then we are setting various properties. You might have noticed when looking at the parameters list in SQL Server that some parameters were numeric and others were various flavors of char (nchar and nvarchar, to be exact). These parameters require an additional property setting, the Parameter.Size property. You also have other properties available, such as the Direction property, which you can set to determine whether a value is for input or output.

With prm

.Name = "EmployeeID"

.Type = adInteger

.Value = Cells(RowNum, 1).Value

End With colReturn.Add prm

Once the properties are set, we add the prm variable to our colReturn collection.

We reuse the prm variable by reinstantiating it before setting the next set of properties and adding to the collection. This is an effective way of reusing an object and ensures you don't have any "leftover" property settings lingering.

This process is repeated for each input parameter that uspUpdateEmployeePersonalInfo requires us to provide. Finally, we set the function's return value to the internal collection object:

Set SetParams = colReturn

Next, we'll finish setting up the Command object and loop through the collection of Parameter objects, appending each to the Command object's Parameters collection:

With cmd

.CommandType = adCmdStoredProc

.CommandText = "HumanResources.uspUpdateEmployeePersonalInfo" For i = 1 To colParams.Count

.Parameters.Append colParams(i) Next i

End With cmd.Execute

We end by calling the Command.Execute method to send the updated data to the database. Before we run this command, let's take a look at the entire procedure. It should look like this:

Sub UpdateEmpPersonalInfo() Dim cnn As ADODB.Connection Dim cmd As ADODB.Command Dim colParams As Collection Dim sConnString As String Dim i As Integer

Sheets("Sheet1").Activate 'make sure we're on the data sheet

Set cnn = New ADODB.Connection sConnString = "Provider=SQLNCLI;Server=MYSERVERNAME\SQLEXPRESS;" * & "Database=AdventureWorks;Trusted_Connection=yes;"

cnn.Open sConnString

Set cmd = New ADODB.Command cmd.ActiveConnection = cnn

Set colParams = SetParams(ActiveCell.Row)

With cmd

.CommandType = adCmdStoredProc

.CommandText = "HumanResources.uspUpdateEmployeePersonalInfo" For i = 1 To colParams.Count

.Parameters.Append colParams(i) Next i

End With cmd.Execute cnn.Close

Set colParams = Nothing Set cmd = Nothing Set cnn = Nothing

MsgBox "Record has been updated", vbOKOnly, "Record Processed" End Sub

Now we'll modify some data and run the procedure. Figure 2-29 shows the data before we make any changes.

Figure 2-29. Employee data before update

Figure 2-29. Employee data before update

Kevin Brown, EmployeelD 2, has been recently married. Change his marital status to M, and then move the cursor to save the change. Run the UpdateEmpPersonalInfo routine, making sure the cursor is in the row containing Kevin's record. The "Record has been updated" message will appear.

To test your success, select and delete all the data from Sheetl (or just change Kevin's marital status to any character), and run the GetEmpList subroutine again. Your display should look like Figure 2-30.

Figure 2-30. Employee data after update

Figure 2-30. Employee data after update

0 -2

Post a comment