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.

5.3.1. 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. The Left function sits in the middle of the Insert statement and ensures that no name is longer than 10 characters:

	Dim myDB As ADODB.Connection
	Set myDB = CurrentProject.Connection
	Dim rsNewPatients As ADODB.Recordset
	Set rsNewPatients = New ADODB.Recordset
	rsNewPatients.Open ("Select * from NewPatients"), myDB
	Do Until rsNewPatients.EOF
	myDB.Execute ("Insert Into Patients Values ('" & _
		 Left(rsNewPatients.Fields("Patient"), 10) & _
		 "', " & rsNewPatients.Fields("Age") & ")")
	rsNewPatients.MoveNext
	Loop
rsNewPatients.Close
myDB.Close
Set myDB = Nothing

The Left function cuts the size of the name to 10 characters. Another option, of course, is to increase the size of the table field.

5.3.2. Watching Out for Apostrophes

Nothing disrupts an Insert faster than the odd apostrophe or single quotation mark. It's reasonable to have these in your data; after all, the name O'Reilly has one. But in a SQL Insert, the single quote qualifies text. Therefore, without a little help, this Insert operation will fail:

	"Insert Into Patients (Patient, Age) Values (Left('O'Reilly',10), 22)"

The problem is that as the statement is executed, the single quote before the letter O starts the text and the single quote after the letter O ends the text. This leaves the Reilly part of the name as unidentifiable.

Doubling up single quotes removes the problem, and the way to do this is to use the Replace function. Replace replaces each instance of a single quote with two single quotes. Here is the previous code routine modified to handle single quotes:

	Dim myDB As ADODB.Connection
	Set myDB = CurrentProject.Connection
	Dim rsNewPatients As ADODB.Recordset
	Set rsNewPatients = New ADODB.Recordset
	rsNewPatients.Open ("Select * from NewPatients"), myDB
	Do Until rsNewPatients.EOF
	  myDB.Execute ("Insert Into Patients Values ('" & _
         Replace(rsNewPatients.Fields("Patient"), "'", "''") & _
         "', " & rsNewPatients.Fields("Age") & ")")
	rsNewPatients.MoveNext
	Loop
	rsNewPatients.Close
	myDB.Close
	Set myDB = Nothing

Here is how to use the Replace function:

	Replace(rsNewPatients.Fields("Patient"), "'", "''")

Replace works by testing a string of text for one or more characters. If the string is found, it is replaced with another string of one or more characters. The three function arguments are:

The string being searched
The characters being searched for
The replacement string

All data coming from the NewPatients Patient field is tested for the single quote, and if it's found, the quote is replaced with two single quotes. This creates an acceptable SQL statement, and the insert can proceed.

5.3.3. Combining the Two Validations

I left the best for last. You need to test for both excessive length and apostrophes. Can you test for both simultaneously? You bet! Just nest one function inside the other with the following code:

	myDB.Execute ("Insert Into Patients Values ('" & _
       Left(Replace(rsNewPatients.Fields("Patient"), "'", "''"), 10) & _
       "', " & rsNewPatients.Fields("Age") & ")")