Write Permission

Even if a Recordset object is a dynaset or table type object, which are normally updatable, the tables underlying a Recordset object may not permit you to modify data. Check the Updatable property of the Recordset object to determine whether its data can be changed. If the property is True, the Recordset object can be updated.

Individual fields within an updatable Recordset object may not be updatable, so attempting to write to these fields can generate a run-time error. To determine whether a given field is updatable, check the DataUpdatable property of the corresponding Field object in the Fields collection of the Recordset object.

In the following example, a Recordset object variable is passed to a function that first determines whether the Recordset object is updatable. If it is, the function counts how many Field objects are updatable and returns the total. It also prints the Name property of each Field object that is not updatable. If all the fields are updatable, the function returns  – 1. If the entire Recordset object is not updatable, the function returns 0. If some of the fields are updatable, the function returns the number of updatable fields.

Function UpdateTest(rst As Recordset) As Integer
	Dim intCount As Integer, intX As Integer
	Dim fld As Field

	' Initialize counter variable.
	intCount = 0
	' Check whether recordset is updatable.
	If rst.Updatable Then
		' Check whether each field is updatable.
		For Each fld In rst.Fields
			If fld.DataUpdatable Then
				' Increment counter for each updatable field.
				intCount = intCount + 1
			Else
				' Print nonupdatable fields.
				Debug.Print fld.Name
			End If
		Next fld
		If intCount = rst.Fields.Count Then
			' If all fields are updatable, return -1.
			UpdateTest = -1
		Else
			' If some fields are updatable, return number.
			UpdateTest = intCount
		End If
	Else
		' If no fields are updatable, return 0.
		UpdateTest = 0
	End If
End Function