| As yet, this book has touched only very lightly on the topic of using recordsets to store data in a database. There are more efficient methods, discussed in the prior sections of this chapter as well as in earlier chapters. When you can use a Select query to return fields and records from a database, by all means do so. When you can use an existing action query, or create one temporarily, to modify records and fields, that's usually the method to use. SQL-based queries make more efficient use of your system's resources than do recordsets. Append queries are fine when you want to add a group of records to a database table without a lot of intermediate processing. Delete queries work well when you want to remove records, or an easily identifiable group of records, from a table. Update queries are a good choice when you want to take the same action on some or all the records in a table. But when you need your code to examine each record individually and perhaps take action conditionally, you probably need to use a recordset. Suppose that you want to put new records into a database table or to change the value of one or more fields in an existing record. These two very common actions call for the recordset's AddNew method and its Edit method; in DAO, both these methods require that you also use the recordset's Update method. The next two sections show examples of these methods. Adding Data to a Recordset There are many reasons that you would want to add data to a database from the Excel platform. One of the more important reasons is that although you or your users prefer to enter data directly into Excel, a database makes much more sense than a worksheet for long-term storage. The other reason of importance is that you want to use one or more of Excel's worksheet functions before storing the data elsewhere. Suppose that you're tracking the performance of stocks in a retirement account. You find it useful to chart their daily per-share values against some broad-based index such as the S&P 500 or the NASDAQ, and to include trendlines on the charts. This is a fairly basic task in Excel, but clumsy at best in most database applications. So, you decide to perform your analysis in Excel and from time to time put the stock data into an Access database. You could automate that process with the following code: Sub AddStocks() Dim dbStocks As DAO.Database Dim rs401k As DAO.Recordset Dim wksStocks As WorkSheet Dim i As Integer Dim FinalRow As Long Set dbStocks = OpenDatabas(ThisWorkbook.Path & _ "\Stocks.mdb") Set rs401k = dbStocks.TableDefs("Retirement") _ .OpenRecordset(dbOpenDynaset) Set wksStocks = ThisWorkbook.Worksheets("2004") FinalRow = wksStocks.Cells(65536,1).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 procedure named AddStocks begins by opening a DAO database named Stocks.mdb. It establishes a recordset based on a table named Retirement, located in Stocks.mdb. It uses a For-Next loop to add new records to the recordset, and thus to the table that the recordset represents. Each time a new record is added by means of the AddNew method, three fields in that new record are populated with values found in columns A, B, and C of the worksheet named 2004. The name of the stock is obtained from column A, the date on which the stock was priced from column B, and the price itself from column C. After the three fields get their values, the Update method is called. When you use the AddNew method (or, as shown next, the Edit method), a record is placed in a buffer and changes are made to the record in the buffer. Only when you call the Update method are the record and the changes you've made to it moved from the buffer to the underlying data source. After the current record has been updated, the loop continues, establishing a new record, populating its fields, and moving the record from the copy buffer into the Retirement table. Editing Existing Data When you already have all the necessary records available and the sole task is to edit the values in their fields, consider using an Update query. Suppose that you sell 100 shares of each holding in your retirement account. Because you're selling the same number of shares regardless of the holding, an Update query makes sense. You might provide SQL along these lines: strSellShares = "UPDATE Retirement SET " & _ "Retirement.SharesHeld = Retirement.SharesHeld 100;"
But if you wanted to record that you sold a different number of shares in each stock, an Update query might not make sense, and processing a recordset might be much more attractive. Suppose that your worksheet has the name of each stock in your retirement account in column A, and the number of shares you have sold in column B. Then the code might look like this: Sub SellStocks() Dim dbStocks As DAO.Database Dim rs401k As DAO.Recordset Dim wksStocks As Worksheet Dim i As Integer Dim FinalRow As Long Set dbStocks = OpenDatabase(ThisWorkbook.Path & _ "\Stocks.mdb") Set rs401k = dbStocks.TableDefs("Retirement") _ .OpenRecordset(dbOpenDynaset) Set wksStocks = ThisWorkbook.Worksheets("2004") FinalRow = wksStocks.Cells(65536,1).End(xlUp).Row With rs401k For i = 2 To FinalRow .FindFirst "StockName = '" _ & wksStocks.Cells(i, 1) & "'" If Not .NoMatch Then .Edit .Fields("SharesHeld") = _ .Fields("SharesHeld") _ wksStocks.Cells(i, 2) .Update Else wksStocks.Cells(i,3) = _ "Could not find this stock in database." End If Next i End With End Sub
The procedure named SellStocks differs in two fundamental ways from AddStocks. When stocks were being added to the database, it wasn't necessary to find a particular stock: The code just adds each stock's name, date, and price to the table. But SellStocks edits the existing records, and (as is typical when you're editing existing data) it has to start by finding the record to be edited. So, the first major difference between the two procedures is that SellStocks uses the FindFirst method to locate each stock. It obtains the name of the stock from the worksheet and finds its record in the database. After the record has been found, it edits the record by subtracting a number, also taken from the worksheet, from the SharesHeld field. The FindFirst syntax is Recordset.FindFirst Criterion
where Criterion is a string that consists of a field name, an operator, and a value to find. For example: strCriterion = "SharesHeld = '" & _wksStocks.Cells(i, 1) & "'" rs401k.FindFirst strCriterion
So, to use it, simply name the recordset that you want to search, followed by FindFirst. Supply a field name, an operator, and the value that you want to find. You don't need to use the equal sign as the operator: You can also use less than, greater than, does not equal, and so on. The other major difference between the two procedures is that SellStocks has to make a provision for the possibility that a stock name found on the worksheet does not exist in the database. Therefore the NoMatch property is used. NoMatch is a property that belongs to DAO recordsets. It's used after a Find (in SellStocks, it's used after FindFirst). If your code is successful in finding a record meeting the criterion, NoMatch is set to False. If your code cannot find such a record, NoMatch is set to True. You should provide for the case in which a record cannot be found. In the SellStocks procedure, the code writes a message that the stock couldn't be found. It puts that message on the same row as the stock's name, in the third column. NOTE There are four Find methods in DAO. FindFirst starts at the beginning of a recordset and looks toward the end for a matching record. FindLast starts at the end and looks toward the beginning. FindPrevious looks from the current record toward the start of a recordset. FindNext looks from the current record toward the end.
| 