Prevent failed append operations so that all the records make it into the table.
You use the SQL
Insert statement to append records to a table. Although this usually works great, it is prone to issues that can make it bomb. This hack shows two things you can do to validate data before handing it off to an
Insert operation. Before we discuss these validation methods, let's create a simple table, as shown in Figure 5-4.
Figure 5-4. A table that accepts names and ages
The table has two fields:
Meant to take just the first name; the length is set to 10.
The age of the patient.
One thing that can trip up an
Insert is trying to stick data into a field when the data is longer than the field length. This is an issue only for text fields. Here is an
Insert statement that works fine:
"Insert Into Patients (Patient, Age) Values ('Gary', 22)"
Gary fits in the Patient text field. Now, look at this statement:
"Insert Into Patients (Patient, Age) Values ('Bartholemew', 22)"
Uh-oh. The name
Bartholemew is 11 characters long, but the Patient field can accept a maximum of only 10 characters. The easiest way to fix statements such as these is to truncate the text to 10 characters by using the
Here is a sample code routine that appends records from the NewPatients table to the Patients table. The ...