Updatable Property

Applies To

Database Object, Dynaset-Type Recordset Object, QueryDef Object, Recordset Object, Snapshot-Type Recordset Object, Table-Type Recordset Object, TableDef Object.

Description

Returns a value that indicates whether changes can be made to a data access object. True (-1) indicates that the object can be changed or updated. (Snapshot-type Recordset objects always return False (0).)

Remarks

Depending on the object, if the Updatable property setting is True, the specified:

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

You can use the Updatable property with all Recordset objects.

The Updatable property setting is always True for a newly created TableDef object and False for an attached 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 nonupdatable 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 nonupdatable 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 nonupdatable fields, check the Fields collection of a Recordset object to check the DataUpdatable property of each field before you edit a record in the Recordset object.

See Also

Field Object.

Example

This example adds a record to a Recordset object if the object's Updatable property setting is True.

Dim dbsNorthwind As Database, rstUnknown As Recordset
Set dbsNorthwind =  DBEngine.Workspaces(0).OpenDatabase("Northwind.mdb")

Set rstUnknown = dbsNorthwind.OpenRecordset("Unfamiliar Table")
If rstUnknown.Updatable = True Then
    rstUnknown.AddNew
    rstUnknown("MyField") = "Some new data"
    rstUnknown.Update
End If
dbsNorthwind.Close

Example (Microsoft Access)

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

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

    ' Return Database variable that points to current database.
    Set dbs = CurrentDb
    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
    dbs.Close
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