Access Hacks

Hack #41. Create Bulletproof Insert Operations

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:

Patient

Meant to take just the first name; the length is set to 10.

Age

The age of the patient.

Handling Excessive Text Length

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)"

The name 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 Left function.

Here is a sample code routine that appends records from the NewPatients table to the Patients table. ...