| There are some minor differences between DAO and ADO when it comes to modifying data. This section examines how you might structure your code using ADO to perform the two tasks discussed in the prior section, adding records and editing existing records. Using ADO to Add Records As before, the intent is to scan the data on a worksheet, picking up the names and share prices of stocks and copying them to a database table. The declarations in the subroutine are of course somewhat different in ADO. A new connection is established (the cnn object) and used to open the recordset. Sub AddStocks() Dim cnn As New ADODB.Connection Dim rs401k As ADODB.Recordset Dim wksStocks As Worksheet Dim i As Integer Dim FinalRow As Long cnn.Open _ ConnectionString:="Provider=SQLOLEDB.1;" & _ "Data Source=(local);" & _ "Initial Catalog=NorthwindCS;Integrated Security=SSPI" Set rs401k = New ADODB.Recordset rs401k.Open "Retirement", cnn, adOpenStatic, _ adLockOptimistic, adCmdTable
The prior statement contains a lot of functionality, as follows: It names the data source for the recordset: the Retirement table, as before. It supplies the connection object, cnn, so that the recordset can find the data source. It sets the recordset cursor to adOpenStatic. This setting means that the recordset can be modified, and you can move back and forth through it. You would not be able to see changes that other users might make. It sets record locking to optimistic: Your code will be able to modify the data, and any other users would be able to modify the same record that you're working on. When your modifications are saved, the record is locked until the database has finished saving the record. Via the adCmdTable option, it informs ADO that the source, already specified as Retirement, is a table or a Select query. Set wksStocks = ThisWorkbook.Worksheets("2004") FinalRow = wksStocks.Cells(65536, 4).End(xlUp).Row With rs401k For i = 2 To FinalRow .AddNew .Fields("StockName") = wksStocks.Cells(i, 1) .Fields("PriceDate") = wksStocks.Cells(i, 2) .Fields("Price") = wksStocks.Cells(i, 3) .Update Next i End With End Sub
The code that actually adds the records looks identical to the DAO code, but there's a slight difference. Using ADO, the Update method isn't required. If you omit Update in DAO, following an AddNew or an Edit, the compiler will generate an error message. This isn't the case in ADO, which treats Update as an optional statement. After a record has been added or edited, the update process occurs automatically when you move to a different record. (When you add a new record, it becomes the current record and you have therefore moved.) If you don't move to a different record, the changes you make aren't saved. For example, suppose that your worksheet had data on stocks in rows 2 through 51, and that you omit the Update method in the code. For rows 2 through 50, the loop moves you to a new record each time it runs, so the automatic update occurs and the data is saved correctly. However, although the data in row 51 would be put into a new record, the record would not be saved because the loop is finished and doesn't move to a different record. Therefore the automatic update doesn't occur. All of this is a lengthy way to say that although the compiler will let you get away without using Update, use it anyway. Using ADO to Edit Records In the following version of the SellStocks procedure, the declarations and preparations are identical to those in the prior AddStocks procedure using ADO instead of DAO: Sub SellStocks() Dim cnn As New ADODB.Connection Dim rs401k As ADODB.Recordset Dim wksStocks As Worksheet Dim i As Integer Dim FinalRow As Long cnn.Open _ ConnectionString:="Provider=SQLOLEDB.1;Data Source=(local);" & _ "Initial Catalog=NorthwindCS;Integrated Security=SSPI" Set rs401k = New ADODB.Recordset rs401k.Open "Retirement", cnn, adOpenStatic, adLockOptimistic, adCmdTable Set wksStocks = ThisWorkbook.Worksheets("2004") FinalRow = wksStocks.Cells(65536, 1).End(xlUp).Row
There are more obvious differences in the loop using ADO when existing records are being edited. One is that instead of a FindFirst method, ADO has a Find method. Its full syntax is Recordset.Find Criteria, SkipRows, SearchDirection, Start
All the arguments apart from Criteria are optional. Provide a numeric value for SkipRows if you want the search to skip over number of records as it checks for a match on the criterion. You can specify adSearchForward or adSearchBackward to control the direction of the search. And if you supply a bookmark, the search will start at that record instead of at the start of the recordset. A real limitation to the use of Find in ADO is that you cannot supply multiple criteria. For example, the following Find would result in a runtime error: Rst.Find "StockName = 'IBM' And StockPrice > 100"
Also notice that the failure to find a record matching the criterion is not tested by NoMatch. Instead, the recordset's BOF or its EOF property becomes True. Which one becomes true depends on the direction you were searching, either adSearchForward a failure results in a True EOF or adSearchBackward a failure results in a True BOF. (BOF stands for Beginning of File and EOF stands for End of File.) With rs401k For i = 2 To FinalRow .Find "StockName = '" _ & wksStocks.Cells(i, 1) & "'" If Not .EOF Then .Fields("SharesHeld") = _ .Fields("SharesHeld") _ - wksStocks.Cells(i, 2) Else wksStocks.Cells(i, 3) = _ "Could not find this stock in database." End If Next i End With End Sub
Although you can search only one field using ADO's Find method, a possible alternative is its Filter method. This would be legal: Rst.Filter = "StockName = 'IBM' And StockPrice > 100"
and would limit the records in the recordset to those with a value of IBM in its StockName field, and with a value on StockPrice that's greater than 100. Notice that the Filter method uses an equal sign, in contrast to the Find method. If, after applying a filter, you need to have all the original records accessible, you can restore them with the adFilterNone constant: Rst.Filter = adFilterNone
Bear in mind that if you use Filter, you might have several records accessible in the recordset. If you want to edit all of them, you'll want to loop through the remaining records. For example, suppose that you wanted to reduce your holdings in IBM by 100 shares in each account, without creating a negative number of shares in any account (selling short is too risky for a retirement portfolio): With rs401k .Filter = "StockName = '" _ & wksStocks.Cells(i, 1) & _ "' And SharesHeld > 100" Do Until .EOF .Fields("SharesHeld") = _ .Fields("SharesHeld") - 100 .MoveNext Loop .Filter = adFilterNone End With
| 