Updatable Property

Applies To Connection object, Database object, Dynamic-Type Recordset object, Dynaset-Type Recordset object, Forward-Only-Type Recordset object, QueryDef object, Recordset object, Snapshot-Type Recordset object, Table-Type Recordset object, TableDef object.

Description

Returns a value that indicates whether you can change a DAO object.

Return Values

The return value is a Boolean data type that is True if the object can be changed or updated. (Snapshot- and forward-only-type Recordset objects always return False.)

Remarks

Depending on the object, if the Updatable property setting is True, the associated statement in the following table is true.

Object	Type indicates

Database	The object can be changed.
QueryDef	The query definition can be changed.
Recordset	The records can be updated.
TableDef	The table definition can be changed.

The Updatable property setting is always True for a newly created TableDef object and False for a linked TableDef object. A new TableDef object can be appended only to a database for which the current user has write permission.

Many types of objects can contain fields that can't be updated. For example, you can create a dynaset-type Recordset object in which only some fields can be changed. These fields can be fixed or contain data that increments automatically, or the dynaset can result from a query that combines updatable and nonupdatable tables.

If the object contains only read-only fields, the value of the Updatable property is False. When one or more fields are updatable, the property's value is True. You can edit only the updatable fields. A trappable error occurs if you try to assign a new value to a read-only field.

The Updatable property of a QueryDef object is set to True if the query definition can be updated, even if the resulting Recordset object isn't updatable.

Because an updatable object can contain read-only fields, check the DataUpdatable property of each field in the Fields collection of a Recordset object before you edit a record.

See Also Field object.

Example

This example demonstrates the Updatable property for a Database, four types of Recordset objects, a TableDef, and a QueryDef.

Sub UpdatableX()

    Dim dbsNorthwind As Database
    Dim rstEmployees As Recordset

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")

    With dbsNorthwind
        Debug.Print .Name
        Debug.Print "    Updatable = " & .Updatable

        ' Default is dbOpenTable.
        Set rstEmployees = .OpenRecordset("Employees")
        Debug.Print _
            "Table-type recordset from Employees table"
        Debug.Print "    Updatable = " & _
            rstEmployees.Updatable
        rstEmployees.Close

        Set rstEmployees = .OpenRecordset("Employees", _
            dbOpenDynaset)
        Debug.Print _
            "Dynaset-type recordset from Employees table"
        Debug.Print "    Updatable = " & _
            rstEmployees.Updatable
        rstEmployees.Close

        Set rstEmployees = .OpenRecordset("Employees", _
            dbOpenSnapshot)
        Debug.Print _
            "Snapshot-type recordset from Employees table"
        Debug.Print "    Updatable = " & _
            rstEmployees.Updatable
        rstEmployees.Close
        Set rstEmployees = .OpenRecordset("Employees", _
            dbOpenForwardOnly)
        Debug.Print _
            "Forward-only-type recordset from Employees table"
        Debug.Print "    Updatable = " & _
            rstEmployees.Updatable
        rstEmployees.Close

        Debug.Print "'" & .TableDefs(0).Name & "' TableDef"
        Debug.Print "    Updatable = " & _
            .TableDefs(0).Updatable

        Debug.Print "'" & .QueryDefs(0).Name & "' QueryDef"
        Debug.Print "    Updatable = " & _
            .QueryDefs(0).Updatable

        .Close
    End With

End Sub

Example (Microsoft Access)

The following example adds a record to a Recordset object if the object's Updatable property setting is True ( – 1).

Sub UpdateData()
    Dim dbs As Database, rstUnknown As Recordset

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Open table-type Recordset object.
    Set rstUnknown = dbs.OpenRecordset("Unfamiliar Table")
    ' Check Updatable property before adding new record.
    If rstUnknown.Updatable = True Then
        With rstUnknown
            .AddNew
            !SomeField = "Some new data"
            .Update
        End With
    End If
    rstUnknown.Close
    Set dbs = Nothing
End Sub

Example (Microsoft Excel)

This example prompts the user to select a cell that contains a value for the CONTACT field of the Customer recordset in the Nwindex.mdb database. The example then checks to see whether the recordset can be updated. If so, the example adds a new record to the recordset, using the value in the selected cell.

To create the Nwindex.mdb database, run the Microsoft Excel example for the CreateDatabase method.

Dim db As Database, rs As Recordset
Sheets("Sheet1").Activate
cellToCopy = Application.InputBox("What cell value do you want" _
    & " to update as contact?", Type:=8)
If cellToCopy = False Then        ' User cancelled InputBox.
    Exit Sub
End If
Set db = Workspaces(0).OpenDatabase(Application.Path & "\NWINDEX.MDB")
Set rs = db.OpenRecordset("Customer")
If rs.Updatable = True Then
    rs.AddNew
    rs("CONTACT") = cellToCopy
    rs.Update
    rs.MoveLast
    MsgBox "The new contact is " & rs("CONTACT").Value
Else
    MsgBox "The recordset cannot be modified."
End If
rs.Close
db.Close