| Human resources isn't through with you yet. You've been asked to create a form that directors can use to view information about employees in their departments: people whose annual performance appraisals are due during the next month, dates that annual physicals are required, employees returning from leaves of absence, and so on. Creating the form is not the real problem. The problem is to get the data for the form out of HR's secure employee database and into a database that can be more broadly accessed by various users here, the department directors. Normally this would be a simple task: just point a Select query at the appropriate table in HR's database and use it as the data source for a user form. The problem is that you can't point a Select query at the HR database: It stores its data in a proprietary format that isn't compliant with today's standards. The program that HR uses is an old one. But it does have a report writer that you use periodically to create a CSV file. With the CSV file created, you can use it to populate a database table. There's one further wrinkle: You need to create a new field as part of the process. The HR application will provide you with the date that an employee's next appraisal is due. But the directors have a grace period for filing the annual appraisal. The grace period extends from the appraisal's due date to four weeks following the due date. That is, if Ms. Smith's appraisal is due on August 9, her appraisal is not regarded as overdue until after September 6. So, your project will have to calculate that overdue date in addition to updating the basic employee data. The sequence of events will be as follows: - Use HR's report writer to create a new CSV file.
- Import the text data from the CSV file into a database table.
- Delete the old employee records from their table.
- Append the new employee records to the table. At the same time, you can calculate the appraisal overdue date from the appraisal due date.
Each of these items is discussed in detail in the following sections. Importing the Text Data The first task is to arrange to import the data. Suppose that the HR report writer outputs its text data to a file named Employees.csv. You'll want to import the data into an Access table in such a way that subsequent imports can be automated. You do that by taking these steps. - With the Access database window active, choose File, Get External Data, Import. The Import window appears.
- From the Files of Type dropdown, choose Text Files (*.txt;*.csv;*.tab;*.asc).
- Using the Look In dropdown if necessary, browse to the location of the Employees.csv file.
- Click the Employees.csv file to highlight it and click the Import button. The Import window closes and is replaced by the first step of the Import Text Wizard (see Figure 9.18).
Figure 9.18. Notice that the first row of the data file includes field names. 
- No options need changing in the wizard's first step, so click Next to go to the second step (see Figure 9.19).
Figure 9.19. Access notes the location of the commas and separates the fields into columns accordingly. 
- Your file separates fields with commas, so you can leave the choice of delimiter alone. As shown, field names occupy the first row, so you should fill the First Row Contains Field Names check box. So doing prevents Access from treating a row of field names as a record. Also, the file uses double quotes to set off text values, so use the Text Qualifier dropdown to choose the double quote mark. Click Next to go to the next step (see Figure 9.20).
Figure 9.20. Specifying First Row Contains Field Names in the prior step makes column headers of the field names. 
- Make sure that In a New Table is selected and click the Next button (see Figure 9.21).
Figure 9.21. If necessary, select a field by clicking in its column and change its properties as needed for example, its Name. 
- If your data file did not have field names in its first row, Access proposes default names (Field1, Field2, and so on). Use this step to change those names to something more descriptive, because the names will become the field names in the new table. This is also the right place to indicate a particular data type for each field (Date/Time in particular, because Access won't automatically recognize some date/time formats). Click Next to go to the next step (see Figure 9.22).
Figure 9.22. If your data has a unique record ID, consider using that as the table's primary key. 
- In this example, the table that the data is imported into does not need a primary key, so you would choose No Primary Key. In a different situation, you might either let Access add a primary key (which would usually be named ID and be typed as a Long Integer increment), or choose your own primary key from the dropdown. Click Next (see Figure 9.23).
Figure 9.23. Click the Advanced button to avoid losing all the information you supplied to the wizard. 
- Figure 9.23 shows that you can supply a different name for the new table; otherwise, it will be named according to the name given to the CSV file. In this case, it's important to save the information you have supplied, and you do so by clicking the Advanced button (see Figure 9.24).
Figure 9.24. The ID suffix is set via Options to call for an index, so the wizard proposes a duplicate-value index for the EmpID field. 
- Make any changes you might require using the controls in the Import Specification window. Then be sure to click the Save As button. This will save your specification so that it can be reused later, and you won't have to supply field names, field types, indexing information, and so on. The Save Import/Export Specification window appears (see Figure 9.25).
Figure 9.25. There's seldom a reason to change the default specification name supplied by Access. 
- If necessary, change the specification name that Access supplies and click OK. You are returned to the Import Specification window.
- Click OK to return to the final step of the Import Text Wizard, and click Finish. Access informs you that it has imported the data into the table.
Now, so long as you don't change the structure of the data file deleting and adding fields, putting Text data where the specification expects Date/Time, using a space instead of a comma as the field delimiter you'll be able to reuse the import specification that you saved in step 11. As you'll see, you can refer to it in VBA code to automate the process of bringing new data into the database. Deleting the Old Records The next task is to prepare to delete the existing records. Then you can replace them with the newly imported records. Of course, the solution is to run a Delete query followed by an Append query. The first step is to create the queries. The completed Delete query is shown in Figure 9.26. Figure 9.26. In another situation, you could selectively delete records by supplying a value for a particular field in the Criteria row. 
To create the query shown in Figure 9.26, take these steps: - With the Access database window active, click the Queries tab. Then click the New button. With Design View selected in the New Query list box, click OK.
- The New Query window disappears and the Show Table window appears. Click the Tables tab if necessary.
- Click the EmployeeData table in the Show Table list box, and then click Add to add the table to the query design. This is the only table involved in the query, so you can click Close to remove the Show Table window.
- Choose Query, Delete Query. This changes the new query from a default Select query to a Delete query.
- Click on the asterisk in the EmployeeData table shown in the query's Table Pane. This asterisk is a sort of wild card, standing in for all the table's fields. Drag it to the Field row of the design grid's first column. The query window now appears as in Figure 9.26.
- Choose File, Save. Access prompts you to replace the default name (Query1, Query2, and so on) with a more descriptive one. This example saves the query as Delete From Employee Data.
You'll run the Delete From Employee Data query to remove existing records from the table in preparation for adding new records obtained from the HR application. You'll also want to create a Delete query to dispose of the old records in the Employees table, perhaps named Delete From Employees. Appending the New Records You can add the records most efficiently by means of an Append query, shown in Figure 9.27. Figure 9.27. An Append query can also calculate new field values to append to the target table. 
To create the query shown in Figure 9.27, take these steps: - With the Access database window active, click the Queries tab. Then click the New button. With Design View selected in the New Query list box, click OK.
- The New Query window disappears and the Show Table window appears. Click the Tables tab if necessary.
- Click the Employees table in the Show Table list box, and then click Add to add the table to the query design. Click Close to remove the Show Table window.
- Choose Query, Append Query. The Append window appears (see Figure 9.28). Choose Employee Data from the Table Name dropdown, and click OK. This changes the new query from a default Select query to an Append query that will obtain records from Employees table and append them to the Employee Data table. You're returned to the query design window.
Figure 9.28. When designing an Append query, start with the source table and specify the target table in the Append window. 
- Click on the asterisk in the Employees table shown in the query's Table Pane. Drag it to the Field row of the design grid's first column.
- In the second column of the design grid, enter in the expression OverdueDate: DateAdd("ww",4,DueDate). This uses the Access function DateAdd to calculate a field named OverdueDate. In this case, it adds four weeks (the ww argument) to the DueDate field. The query window now appears as in Figure 9.27.
- Choose File, Save. Replace the default name with a more descriptive one. This example saves the query as Append To EmployeeData.
Executing the Queries With these two queries in the database, you can run VBA code similar to the following listing: Sub UpdateEmployeeData() Dim PathName As String CurrentDb.QueryDefs("Delete From Employee Data").Execute CurrentDb.QueryDefs("Delete From Employees").Execute PathName = "C:\Documents and Settings\Owner\Desktop\Employees.csv" DoCmd.TransferText acImportDelim, "Employees Import Specification", _ "Employees", PathName, True CurrentDb.QueryDefs("Append To Employee Data").Execute End Sub
There are some aspects to this code that deserve a mention: The TransferText method of the DoCmd object is used to import the data from the CSV file. Its acImportDelim argument indicates that Access is to import a delimited file. The import specification, Employees Import Specification, is identified, as is the Employees table into which the data will be inserted. The path to and the name of the CSV file is stored in a PathName variable and passed to the TransferText method. This approach isn't necessary, but it does make it easier to change the path or filename when necessary. You can execute a query, just as the code shown earlier executes three queries, simply by naming the query and calling the Execute method. The query can be of any type a Select or an action query. By attaching this code to a command button on a user form in the database, you make it very easy to update a table when necessary. You can run this code directly in an Access VBA module. You can also run it (with some adjustments, such as identifying the database) using an Excel VBA module. You would first need to set a reference to a DAO or ADO object library, by choosing References from the VBE Tools menu and filling the appropriate library's check box.
You really don't want to have to jump through such hoops just to get data into a database. It's much more sensible to make direct use of the database or other application that's used to maintain the original data set. But there are times when that isn't feasible. It's often the case, for example, that the data maintenance package can be used on only a few workstations, for licensing reasons. Even then, you would hope that the application supported a linkage from a more widely available application, such as Access or Excel. But many applications don't provide that degree of compatibility or convenience. Then you have to resort to methods discussed in this chapter, as kludgy as they seem. In my own experience, they turn out to be necessary more often than not. |
