Field2 Object (DAO)

Office 2013 and later

Other Versions

Office 2010

Last modified: July 01, 2011

Applies to: Access 2013 | Office 2013

In this article
Remarks
Example
About the Contributors

A Field2 object represents a column of data with a common data type and a common set of properties.

Remarks

A Field2 object is contains all of the same properties and methods as the Field object. The Field2 object contains several new properties and methods that support multi-valued field types. The new properties and methods are:

AppendOnly property
ComplexType property
IsComplex property
LoadFromFile method
SaveToFile method

To refer to a Field2 object in a collection by its ordinal number or by its Name property setting, use any of the following syntax forms:

Fields(0)

Fields("name")

Fields![name]

With the same syntax forms, you can also refer to the Value property of a Field2 object that you create and append to a Fields collection. The context of the field reference will determine whether you are referring to the Field2 object or the Value property of the Field object.

Example

The following example shows how to navigate a Recordset that contains a multi-value field.

Sample code provided by: The Microsoft Access 2010 Programmer’s Reference | About the Contributors

VBA

Sub PrintStudentsAndClasses()
    Dim dbs As DAO.Database
    Dim rsStudents As DAO.Recordset2  'Recordset for students
    Dim rsClasses As DAO.Recordset2  'Recordset for classes
    Dim fld As DAO.Field2

    'open the database
    Set dbs = CurrentDb()

    'get the table of students
    Set rsStudents = dbs.OpenRecordset("tblStudents")

    'loop through the students
    Do While Not rsStudents.EOF
        
        'get the classes field
        Set fld = rsStudents("Classes")

        'get the classes Recordset
        'make sure the field is a multi-valued field before
        'getting a Recordset object
        If fld.IsComplex Then
            Set rsClasses = fld.Value
        End If

        'access all records in the Recordset
        If Not (rsClasses.BOF And rsClasses.EOF) Then
            rsClasses.MoveLast
            rsClasses.MoveFirst
        End If

        'print the student and number of classes
        Debug.Print rsStudents("FirstName") & " " & rsStudents("LastName"), _
            "Number of classes: " & rsClasses.RecordCount

        'print the classes for this student
        Do While Not rsClasses.EOF
            Debug.Print , rsClasses("Value")
            rsClasses.MoveNext
        Loop

        'close the Classes Recordset
        rsClasses.Close

        'get the next student
        rsStudents.MoveNext

    Loop
    
    'cleanup
    rsStudents.Close

    Set fld = Nothing
    Set rsStudents = Nothing
    Set dbs = Nothing

End Sub

The following example shows how to navigate the files in an attachment field. The file type and filename of each attachment is printed in the Immediate window.

VBA

Sub ListAttachments()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset2
    Dim rsA As DAO.Recordset2
    Dim fld As DAO.Field2
    
    'Get the database, recordset, and attachment field
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblAttachments")
    Set fld = rst("Attachments")
    
    'Navigate through the table
    Do While Not rst.EOF
    
        'Print the first and last name
        Debug.Print rst("FirstName") & " " & rst("LastName")
        
        'Get the recordset for the Attachments field
        Set rsA = fld.Value
        
        'Print all attachments in the field
        Do While Not rsA.EOF
            Debug.Print , rsA("FileType"), rsA("FileName")
            
            'Next attachment
            rsA.MoveNext
        Loop
        
        rsA.Close
        
        'Next record
        rst.MoveNext
    Loop
    
        
    rst.Close
    dbs.Close
    
    Set fld = Nothing
    Set rsA = Nothing
    Set rst = Nothing
    Set dbs = Nothing
End Sub

The following example shows how to add files from a specified folder path to an attachment field.

VBA

Public Function LoadAttachments(strPath As String, Optional strPattern As String = "*.*") As Long
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset2
    Dim rsA As DAO.Recordset2
    Dim fld  As DAO.Field2
    Dim strFile As String
    
    'Get the database, recordset, and attachment field
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblAttachments")
    Set fld = rst("Attachments")
    
    'Navigate through the table
    Do While Not rst.EOF
    
        'Get the recordset for the Attachments field
        Set rsA = fld.Value
        
        'Load all attachments in the specified directory
        strFile = Dir(strPath & "\*.*")
        
        rst.Edit
        Do While Len(strFile) > 0
            'Add a new attachment that matches the pattern.
            'Pass "" to match all files.
            If strFile Like strPattern Then
                rsA.AddNew
                rsA("FileData").LoadFromFile strPath & "\" & strFile
                rsA.Update
                
                'Increment the number of files added
                LoadAttachments = LoadAttachments + 1
            End If
            strFile = Dir
        Loop
        rsA.Close
        
        rst.Update
        'Next record
        rst.MoveNext
    Loop
    
    rst.Close
    dbs.Close
    
    Set fld = Nothing
    Set rsA = Nothing
    Set rst = Nothing
    Set dbs = Nothing
End Function

The following example shows how to save the files stored in an attachment field to the specified folder path.

VBA

Public Function SaveAttachments(strPath As String, Optional strPattern As String = "*.*") As Long
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset2
    Dim rsA As DAO.Recordset2
    Dim fld As DAO.Field2
    Dim strFullPath As String
    
    'Get the database, recordset, and attachment field
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblAttachments")
    Set fld = rst("Attachments")
    
    'Navigate through the table
    Do While Not rst.EOF
    
        'Get the recordset for the Attachments field
        Set rsA = fld.Value
        
        'Save all attachments in the field
        Do While Not rsA.EOF
            If rsA("FileName") Like strPattern Then
                strFullPath = strPath & "\" & rsA("FileName")
                
                'Make sure the file does not exist and save
                If Dir(strFullPath) = "" Then
                    rsA("FileData").SaveToFile strFullPath
                End If
                
                'Increment the number of files saved
                SaveAttachments = SaveAttachments + 1
            End If
            
            'Next attachment
            rsA.MoveNext
        Loop
        rsA.Close
        
        'Next record
        rst.MoveNext
    Loop
    
    rst.Close
    dbs.Close
    
    Set fld = Nothing
    Set rsA = Nothing
    Set rst = Nothing
    Set dbs = Nothing
End Function

The following example shows how to delete a file stored in an attachment field.

VBA

Function RemoveAttachment(strRemoveFile As String, Optional strFilter As String) As Long
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset2
    Dim rsA As DAO.Recordset2
    Dim fld As DAO.Field2
    
    'Get the database
    Set dbs = CurrentDb
    
    'Open the recordset. If the strFilter is supplied, add it to the WHERE
    'clause for the recordset. Otherwise, any files matching strFileName
    'will be deleted
    If Len(strFilter) > 0 Then
        Set rst = dbs.OpenRecordset("SELECT * FROM tblAttachments WHERE " & strFilter)
    Else
        Set rst = dbs.OpenRecordset("tblAttachments")
    End If
    
    'Get the Attachment field
    Set fld = rst("Attachments")
    
    'Navigate through the recordset
    Do While Not rst.EOF
    
        'Get the recordset for the Attachments field
        Set rsA = fld.Value
        
        'Walk the attachments and look for the file name to remove
        Do While Not rsA.EOF
            If rsA("FileName") Like strRemoveFile Then
                rsA.Delete
                
                'Increment the number of files removed
                RemoveAttachment = RemoveAttachment + 1
            End If
            rsA.MoveNext
        Loop
                
        'Cleanup the Attachments recordset
        rsA.Close
        Set rsA = Nothing
        
        'Next record
        rst.MoveNext
    Loop
    
    rst.Close
    dbs.Close
    Set fld = Nothing
    Set rst = Nothing
    Set dbs = Nothing
End Function

About the Contributors

Wrox Press is driven by the Programmer to Programmer philosophy. Wrox books are written by programmers for programmers, and the Wrox brand means authoritative solutions to real-world programming problems.