Loading Files into Attachment Fields

, jL. ^ y- 7; r^-fiVfjfij The Recordset2 object (new to Access 2007) is used to work with fields of the l™^®™^™»«»™™ Attachment type.

The LoadAttachments procedure listed next iterates through the documents in the folder selected by the Input Documents Path button on the main menu, and for any document that starts with "Contact ID" saves the document to the corresponding contact records File field (this field is of the Attachment data type). An Attachment field can contain multiple attachments, and the collection of attachments is represented in VBA code as a separate recordset of attachments belonging to a record in a table. Using a Recordset2 object (new to Access 2007) to work with the attachments lets you use the new LoadFromFile and SaveToFile methods to work with the attachments.

Figure 9.19 shows a folder with Contact ID documents of various types for loading as attachments.


A folder with some Contact ID documents, for creating attachments.


A folder with some Contact ID documents, for creating attachments.

In the LoadAttachments function listed as follows, declaring the rstAttachments variable as a Recordset2 object (instead of a Recordset object) allows use of the new LoadFromFile and SaveToFile methods, which I use to load files into Attachment fields, or save files from Attachment fields:

Public Function LoadAttachments()

On Error GoTo ErrorHandler

Dim intSpace As Integer Dim strTest As String Dim strSearch As String strDocsPath = GetInputDocsPath()

Set fso = CreateObject("Scripting.FileSystemObject") Set fld = fso.GetFolder(strDocsPath) Set dbs = CurrentDb

Set rstTable = dbs.OpenRecordset("tblContacts", dbOpenDynaset)

For Each fil In fld.Files strFile = fil.Name Debug.Print "File name: Debug.Print "File type:

& strFile & fil.Type

Check whether file name starts with 'Contact ID'

If Left(strFile, 10) = "Contact ID" Then

Extract Contact ID from file name, using Mid and InStr functions to start at the beginning of the number and end before the space following the number, if there is one.

strTest = Mid(String:=strFile, Start:=12, Length:=3) intSpace = InStr(strTest, " ")

If intSpace > 0 Then lngContactID = CLng(Mid(String:=strTest, _ Start:=1, Length:=intSpace - 1))

Else lngContactID = CLng(strTest) End If strSearch = "[ContactID] = " & lngContactID Debug.Print "Search string: " & strSearch strFileAndPath = strDocsPath & strFile

Search for matching Contact ID in table.

rstTable.MoveFirst rstTable.FindFirst strSearch If rstTable.NoMatch = True Then strTitle = "Can't find contact" strPrompt = "Contact ID " & lngContactID _

& " not found in table; can't add attachment" GoTo NextDoc Else rstTable.Edit

Create recordset of attachments for this record, using the new Recordset2 type recordset.

Set rstAttachments = _


Turn off the error handler to prevent errors if the code attempts to add the same file twice; in this case the Attachments recordset won't be updated.

On Error Resume Next

With rstAttachments .AddNew

.Fields("FileData").LoadFromFile _

(strFileAndPath) .Update .Close End With rstTable.Update

Debug.Print "Added " & strFileAndPath _ & " as attachment to Contact ID " _ & lngContactID; "'s record"

End If

End If


Next fil

Open the Contacts form to see the attachments that have been loaded. DoCmd.OpenForm FormName:="frmContacts"

ErrorHandlerExit: Exit Function


& "; Description: " & Err.Description

End Function

Figure 9.20 shows the main menu of the sample database, Files and Folders.accdb, with the Load Attachments option selected; this selection calls the LoadAttachments function.


The main menu, with an Attachments button with two options, for loading or saving attachments.

BE Main Menu


Files and Folders


Browse Contacts


Send EMails to Contacts

j Load Data from Text Files


(* Load Attachments C SaveAttachments

J Backup



[ Input Documents Path... ]

D:\Documents\Contact Documents

[ Output Documents Path... ]

D:\Documents\Saved Attachments

0 0

Post a comment