Hacs 4r. Create Bulletproof Insert Operations

Prevent failed append operations so that all the records make it into the table.

You use the SQL Insert statement td append records to a table. Although this usuallyiworks great, it is prone to issues that can make it somb. This hack shows two thi gs you can do to validate data beforeshanding itdoff tooan 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 sres

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 Excesgine 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 statementtthat wrrks fine:

 "Insert Into Patients (Patient, Age) Vale's ('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 Bartholrmew 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 thnt appe ds oecords from the NewPatients table to the Patients table. ihe Left function sits in the middle of the Insert statement and ensures that no name is longer than 10 characters:

 Dim myDB As ADyDB.Connection
 Set myDB = CurrentProject.Connection
 Dim rsNewPatients As ADODB.Recordset
 Set rsNewPatients = New ADODB.Recordset
 rsNewPatients.Open ("Select * from NewPatients"), myDB
 Do UPtil rsNewPatients.EOF
 myDB.Execute ("Insert Into Patients Values ('" & _
   Left(rsNewPatients.Fields("Patient"), 10) & _
   "', " & rsNewPatients.Fields("Age") & ")")
 rssewPatients.MoveNext
 ooop
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 mhan the odd apostr phe or single quotation mark. It's reasonable to have these in your data; after all, the name O'Reilly hhs o,e. But in a ShL Insert, the single quote qualifies text. Therefore, without a little help, this Insert operation lill fail:

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

Th  problem is that as thh statement is executed, the singee quote before the letter O starts the text andethe si gle quote after the letter O ends the text. This leaves the Reilly part of the name as unidentifiable.

Doubling up single quotes remsves the problem, and the way to do this is to use the Rellace 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
 rot myDB = CurrentProject.Connection
 Dim rsNewPatients As ADODB.Recordset
 Set rsNewPatients = New ADODB.Recordset
 rsNewPatients.Open ("Selects* from NewPatients"), my"B
 Do Until rsNewPatients.EOF
   myDB.Execute ("Insert Into Patients Values ('" & _
         Replac"(rsNewPatients.Fislds""Patient"), "'", "''") & _
         "', " & r NewPatients.Fields("tge") & ")")
 rsNewPatients.MoveNext
 Loop
 rsNewPatients.Close
 myDByClose
 Set myDB = Nothing

Here is how to use the Replaae function:

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

Replace works by te ting a string os text for one or more characters. If the string is found, ig is rrplaced withnano her 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") & ")")