| As director of an Employee Health Services department, one of your responsibilities is to make sure that the date on which an employee has a physical examination gets recorded in HR's Employees database. You arrange for the receptionist to record the necessary information in an Excel worksheet after the physical has been completed. That information includes the employee's unique ID number, as well as the date and time that the physical was completed (see Figure 12.2). Figure 12.2. Because the records enter the database with no special handling, an action query handles this situation efficiently. 
You use the workbook's BeforeClose event to execute the following code when the receptionist closes it at the end of each business day. Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim dbEmpPhys As DAO.Database Dim qdfTempQuery As DAO.QueryDef Dim strQuery As String Dim i As Integer Dim EmpID As String, PhysDate As Date, PhysTime As Date Dim LastEmployee As Long Set dbEmpPhys = OpenDatabase _ (ThisWorkbook.Path & "\Employees.mdb") Set qdfTempQuery = dbEmpPhys.CreateQueryDef("")
After declaring the necessary variables, your code sets the database and then sets a new, temporary query (note the empty string given as its name). LastEmployee = ThisWorkbook.Sheets("Appointments") _ .Cells(65536,1).End(xlUp).Row
The number of employee records to be processed is obtained by going up from the last row of column A to the lowermost value in that column, and assigning its row number to LastEmployee. A loop now begins, starting with 2 (because the actual data begins in row 2 of the worksheet) and ending with LastEmployee. The data in columns A, B, and C of the loop's current row are assigned to the variables EmpID, PhysData, and PhysTime. For i = 2 To LastEmployee EmpID = ThisWorkbook.Sheets _ ("Appointments").Cells(i, 1) PhysDate = ThisWorkbook.Sheets _ ("Appointments").Cells(i, 2) PhysTime = ThisWorkbook.Sheets _ ("Appointments").Cells(i, 3)
Now the query's SQL is built (the first time through the loop) and rebuilt (on subsequent cycles) using the current values of the employee ID and the date and time of the physical. The query inserts a new record into the table named Physicals, and specifies the fields named StaffID, DateOfPhysical, and TimeOfPhysical. The SQL's VALUES clause provides the values that are to be inserted into those fields. strQuery = "INSERT INTO Physicals" & _ "(StaffID, DateOfPhysical, TimeOfPhysical) " & _ "VALUES ('" & EmpID & "', '" & PhysDate & "', '" & _ PhysTime & "');"
For a record in which the employee's ID is 314, the date is 2/5/2005, and the time is 10:00 AM, the value that's assigned to strQuery is INSERT INTO Physicals (StaffID, DateOfPhysical, TimeOfPhysical) _ VALUES ('314', '2/5/2005', '10:00 AM');
So, each time the loop executes, different values are supplied to the SQL. The nonpersistent query itself has already been set, and only its SQL changes. The change to its SQL is accomplished in the following With block, which also causes the query to execute: With qdfTempQuery .Sql = strQuery .Execute End With Next i ThisWorkbook.Sheets("Appointments") _ .Range(Cells(2, 1), Cells(LastEmployee,3)).Clear End Sub
After the final trip through the loop, the code clears the range that contained the data in preparation for the next day's appointments, and the subroutine terminates. Using DAO to Define and Execute an Update Query When you need to modify many records in a table, altering a field's values in the table, an Update query is often the best choice. Suppose that, following the example introduced in the prior case study, you want to set the date of the next physical for all employees to June 30, 2005. You might use code like this: Sub UpdatePhysicalDates(FilePath As String) Dim dbEmployees As DAO.Database Dim qdfUpdateDates As DAO.QueryDef Dim strUpdateSQL As String
After declaring the necessary variables, your code sets the database. Set dbEmployees = OpenDatabase(FilePath & "Employees.mdb")
Then you assign the appropriate SQL to a string variable. In this case, your SQL calls for all records in the Physicals table to have the value of the NextPhysical field set to 6/30/2005. strUpdateSQL = "UPDATE Physicals SET " & _ "Physicals.NextPhysical = #6/30/2005#;"
You then set the query definition. You make it nonpersistent by naming it with an empty string, and you pass your SQL to the query in the form of the string variable strUpdateSQL. Set qdfUpdateDates = dbEmployees.CreateQueryDef _ ("", strUpdateSQL)
Finally, you execute the query and end the subroutine. qdfUpdateDates.Execute End Sub
This is, of course, unrealistic you would not assign all employees to have their next physical on the same day but it does serve to show how you can assign a constant to all records in a table. A more realistic approach would be to assign all employees a date for their next physical that falls one year following their current physical. You could use exactly the same code as shown before, but you would modify the SQL as follows: strUpdateSQL = "UPDATE Physicals SET " & _ "Physicals.NextPhysical = [DateOfPhysical]+365;"
Now the SQL instructs the database to add 365 to the value of the field named DateOfPhysical, and store the result in the field named NextPhysical. Because no criteria are applied, it carries out the update on all the records in the table. | 