Manipulating Complex Data Types Using DAO

Access 2010 supports complex data types-the Attachment data type or any field defined as multi-value. A complex data type lets you store multiple values or objects in a field within a single record. Access 20107 accomplishes this by building hidden tables that contain one row per multiple value stored. You can manipulate these rows in a recordset in code, but only using DAO.

To work with data in a complex data type field, you must first open a recordset on the table containing the field. You can either open the table directly or open a query that includes the table and its complex field(s). The secret to dealing with complex fields is the Value property of the field in the recordset returns a DAO.Recordset2 object. Therefore, you can set a declared DAO.Recordset2 variable to the Value property to open a recordset on the hidden table. You can manipulate this recordset exactly as you can any other DAO recordset, including using the Find, Move, Edit, AddNew, Update, and Delete methods.

When the complex field is a multi-value field, the recordset returned from the Value property of the parent field contains a single field called Value. You'll find one row per multiple value stored in the complex field. When the complex field is an Attachment data type, the recordset returned from the Value property of the parent field contains three fields- FileData, FileName, and FileType. The FileData field in an attachment complex recordset supports one method, LoadFromFile, that lets you insert the complex Object Linking and Embedding (OLE) data into the record by supplying a file location and name.

The tblContacts table in the Contacts sample database contains both a multi-value field (ContactType) and an attachment field (Photo). In the modExamples module in the Contacts. accdb database, you can find the following code, which displays in the Immediate window the values from both fields for all contact records:

Public Sub ListContactComplex()
' An example of listing all the complex values in the Contacts table
Dim db As DAO.Database, rst As DAO.Recordset, rstComplex As DAO.Recordset2
Dim fld As DAO.Field2
  ' Point to this database
  Set db = CurrentDb
  ' Open a recordset on tblContacts
  Set rst = db.OpenRecordset("SELECT * FROM tblContacts")
  ' Loop through all the records
  Do Until rst.EOF
    ' Dump out the ID and name
    Debug.Print rst!ContactID, rst!LastName, rst!FirstName
    ' Get the contact type complex field
    Set rstComplex = rst!ContactType.Value
    ' Loop through them all
    Do Until rstComplex.EOF
      ' Dump out each value
      Debug.Print " ", "Contact Type: ", rstComplex!Value
      ' Get the next
      rstComplex.MoveNext
    Loop
    ' Get the Photo Attachment recordset
    Set rstComplex = rst!Photo.Value
    ' Loop though them all
    Do Until rstComplex.EOF
      ' Dump out the data
      Debug.Print " ", "Photo FileName: ", rstComplex!FileName, _
        " File Type: ", rstComplex!FileType
      ' Get the next
      rstComplex.MoveNext
    Loop
    ' Get the next contact
    rst.MoveNext
  Loop
  ' Close out
  rst.Close
  Set rst = Nothing
  Set rstComplex = Nothing
  Set db = Nothing
End Sub

If you want to find the record for John Viescas and add the Trainer value to the Contact-Type field, use the following code:

Public Sub AddContactTypeViescas()
Dim db As DAO.Database, rst As DAO.Recordset, rstComplex As DAO.Recordset2
  ' Set a pointer to the current database
  Set db = CurrentDb
  ' Open the contacts table
  Set rst = db.OpenRecordset("tblContacts", dbOpenDynaset)
  ' Find the record for Viescas
  rst.FindFirst "LastName = 'Viescas'"
  ' Make sure we found it
  If Not rst.NoMatch Then
    ' Put parent record in Edit
    rst.Edit
    ' Get the ContactType recordset
    Set rstComplex = rst!ContactType.Value
    ' Add a new row
    rstComplex.AddNew
    ' Insert the new value
    rstComplex.Value = "Trainer"
    ' Save the new value
    rstComplex.Update
    ' Now save the parent
    rst.Update
  End If
  ' Close out
  rst.Close
  Set rst = Nothing
  Set rstComplex = Nothing
  Set db = Nothing

To find the contact record for John Viescas, check for the Trainer value in the ContactType field, and delete it if it exists, use the following code:

Public Sub DeleteTrainerFromViescas()
Dim db As DAO.Database, rst As DAO.Recordset, rstComplex As DAO.Recordset2
  ' Set a pointer to the current database
  Set db = CurrentDb
  ' Open the contacts table
  Set rst = db.OpenRecordset("tblContacts", dbOpenDynaset)
  ' Find the record for Viescas
  rst.FindFirst "LastName = 'Viescas'"
  ' Make sure we found it
  If Not rst.NoMatch Then
    ' Get the ContactType recordset
    Set rstComplex = rst!ContactType.Value
    ' See if Trainer exists
    rstComplex.FindFirst "Value = 'Trainer '"
    ' If it exists,
    If Not rstComplex.NoMatch Then
      ' Delete it
      rstComplex.Delete
    End If
  End If
  ' Close out
  rst.Close
  Set rst = Nothing
  Set rstComplex = Nothing
  Set db = Nothing

To check whether the Photo field for contact Jeff Conrad contains a file named JeffConrad. docx and add it if it does not, use the following code:

Public Sub AddDocumentToConradPhotoField()
Dim db As DAO.Database, rst As DAO.Recordset, rstComplex As DAO.Recordset2
  ' Set a pointer to the current database
  Set db = CurrentDb
  ' Open the contacts table
  Set rst = db.OpenRecordset("tblContacts", dbOpenDynaset)
  ' Find the record for Conrad
  rst.FindFirst "LastName = 'Conrad'"
  ' Make sure we found it
  If Not rst.NoMatch Then
    ' Get the Photo recordset
    Set rstComplex = rst!Photo.Value
    ' See if the JeffConrad.docx file exists
    rstComplex.FindFirst "FileName = 'JeffConrad.docx'"
    ' If it does not exist,
    If rstComplex.NoMatch Then
      ' Put parent record in Edit
      rst.Edit
      ' Start a new attachment record
      rstComplex.Addnew
      ' Load the file
      rstComplex!FileData.LoadFromFile _
	"C:\Microsoft Press\Access 2010 Inside Out\Documents\Jeff Conrad.docx"
      ' Save the new row
      rstComplex.Update
     ' Save the parent row
      rst.Update
    End If
  End If
  ' Close out
  rst.Close
  Set rst = Nothing
  Set rstComplex = Nothing
  Set db = Nothing