Access.2007.VBA.Bibl..

File is already open; close it.

Close #1

Resume

Else

MsgBox “Error No: “ & Err.Number _

& “; Description: “ & Err.Description

Resume ErrorHandlerExit

End If

End Sub

Figure 9.11 shows a typical text file created by the cmdCreateEMails_Clickevent procedure

code (the text file is the same regardless of the method used to create it).

FIGURE 9.11

A text file with information on skipped records.

Figure 9.12 shows one of the email messages created by the previous code.

As is so often the case with Access, you have a choice of several techniques to use when working

with text files in VBA code. Any of the three methods discussed in the next sections can create a

text file and write to it; which method you use depends on such factors as your familiarity with the

technique, or the need for extra references in the database to support the code. I generally use the

FileSystemObjectmethod, partly because I usually have a reference set to the Scripting

Runtime library for other purposes and partly because its syntax is the most intuitive. If your data-

base has a reference to the ADO library, but not the Scripting Runtime library, you can use the

ADO method to avoid the need for setting an extra reference; if you don’t have a reference set to

either the ADO or Scripting Runtime libraries, you can use the VB method to avoid setting an

extra reference.

Part II Writing VBA Code to Exchange Data between Office Components