| Your staff of maintenance technicians inspects fire doors in your company's office buildings on a regular schedule. At the end of each workday, you want the technicians to record the ID of each door that they inspected on that day. There is other information that the technicians are supposed to record, and it turns out that the most convenient location to record all the day's information is in an Excel worksheet, shown in Figure 12.3. Figure 12.3. Excel lends itself well to this sort of informal data entry layout. 
You want to move the doors' inspection date from the worksheet into an Access project. No special handling is needed of the sort that might require you to use a recordset. All you need to do is find each inspected door in the database and update its DateLastInspected field. You decide to use a stored procedure, one that Access would term an Update query. The query is shown in design view in Figure 12.4. Figure 12.4. The funnel in the table, by DoorID, indicates that you've applied a criterion to that field. 
NOTE The construction of stored procedures is in some ways different from the construction of action queries, even though they often accomplish the same ends. Notice in Figure 12.4, for example, that the parameter isn't identified as such by enclosing it in square brackets, but by preceding it with an ampersand. In an Access action query, the parameter's name could have an embedded blank, but you avoid this in a stored procedure. Also note the use of the GETDATE() function in place of the DATE() function. These differences are a direct result of the use of Transact-SQL instead of the SQL version used by Access MDB files.
After the technicians have finished recording the door IDs, they close the workbook. You've set the workbook's BeforeClose event to run the following code just before the workbook closes: Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim cmd As New ADODB.Command Dim cnn As New ADODB.Connection Dim prm As ADODB.Parameter Dim i As Integer Dim LastDoor As Long LastDoor = ThisWorkbook.Sheets("Doors Inspected") _ .Cells(65536, 1).End(xlUp).Row
You set the variable LastDoor to the row in which the final entry is found on the worksheet. In Figure 12.3, that's 19. This will be the final counter value used as the code loops through the door IDs. cnn.Open _ ConnectionString:="Provider=SQLOLEDB.1;" & _ "Data Source=Fran;" & _ "Initial Catalog=FireDoors;Integrated Security=SSPI" Set cmd.ActiveConnection = cnn
You define a connection by means of a string, specifying the provider, the server (the data source), the catalog, and the security. You open the connection and assign it to a Command object. With cmd .CommandText = "RecordLastInspectDate" .CommandType = adCmdStoredProc
You use the Command object's CommandText property to identify the stored procedure to be run, RecordLastInspectDate. You also indicate that the Command object represents a stored procedure by means of acCmdStoredProc. Set prm = .CreateParameter(Name:="WhichID", _ Type:=adVarChar, Size:=10) .Parameters.Append prm
You're going to use the door IDs found on the worksheet as parameter values: That is, you'll submit them to the query one by one in the following loop. Each time through the loop, the query executes after locating the record that's identified by the parameter's value. In setting prm equal to a new parameter, you supply its name, which is the same as the one in the stored procedure. The adVarChar specification means that the parameter's value is a variable-length data type, roughly equivalent to a Text value. The Size parameter sets the maximum length of the value to 10 characters. After you've set the parameter's properties, you append it to the collection of parameters that belong to this Command object. In this case, there's only one parameter in the collection, but it still must be appended. With that parameter in the collection you can use it repeatedly without having to respecify its properties. (Repeated usage is the principal reason for establishing the parameter in this way.) For i = 2 To LastDoor prm.Value = ThisWorkbook.Sheets("Doors Inspected") _ .Cells(i, 1) .Execute Next i End With End Sub
Your code enters the loop that runs through each door ID found on the worksheet. Each time through the loop, the parameter's value is set equal to a different door's ID and the stored procedure is executed. The stored procedure locates the door in its table and sets its LastInspected field to the current date. | 