DAO DataUpdatable not Expected or Not Understanding

[MajP]

I have an autonumber field called PersonID. In code I am trying to determine if a field is updateable. According to MS

The DataUpdatable property returns a value that indicates whether the data in the field represented by a Field object is updatable.

​

Use this property to determine whether you can change the Value property setting of a Field object. This property is always False on a Field object whose Attributes property is dbAutoIncrField.

You can use the DataUpdatable property on Field objects that are appended to the Fields collection of QueryDef, Recordset, and Relation objects, but not the Fields collection of Index or TableDef objectsI read that to say if the field is dbAutoIncrField then it returns false

I run this test

Public Sub Test()
  Dim Rs As DAO.Recordset
  Dim fld As DAO.Field
  Set Rs = CurrentDb.OpenRecordset("Select PersonID from tblDemoUnbound")
  Set fld = Rs.Fields("PersonID")

  Debug.Print "Total Attributes: " & Rs.Fields("PersonID").Attributes
  Debug.Print "Field is Autoincr: " & CStr((Rs.Fields("PersonID").Attributes And 16) = 16)
  Debug.Print "Field is attribute Updateable: " & CStr((Rs.Fields("PersonID").Attributes And 32) = 32)
  Debug.Print "Field is attribute Fixed: " & CStr((Rs.Fields("PersonID").Attributes And 1) = 1)
  Debug.Print
  Debug.Print "PersonID DataUpdatable: " & Rs.Fields("PersonID").DataUpdatable
End Sub

Which verifies the attributes as an autoincrement field, updateable, and fixed size. See attributes table. It returns a 49 which is 32, 16, 1.

Total Attributes: 49
Field is Autoincr: True
Field is attribute Updateable: True
Field is attribute Fixed: True

PersonID DataUpdatable: True

Constant	Value	Description
dbAutoIncrField	16	The field value for new records is automatically incremented to a unique Long integer that can't be changed (in a Microsoft Access workspace, supported only for Microsoft Access database engine database tables).
dbDescending	1	The field is sorted in descending (Z to A or 100 to 0) order; this option applies only to a Field object in a Fields collection of an Index object. If you omit this constant, the field is sorted in ascending (A to Z or 0 to 100) order. This is the default value for Index and TableDef fields (Microsoft Access workspaces only).
dbFixedField	1	The field size is fixed (default for Numeric fields).
dbHyperlinkField	32768	The field contains hyperlink information (Memo fields only).
dbSystemField	8192	The field stores replication information for replicas; you can't delete this type of field (Microsoft Access workspace only).
dbUpdatableField	32	The field value can be changed.
dbVariableField	2	The field size is variable (Text fields only).\

Even though the attribute returns autoincr the DataUpdatable still shows as true which is not as MS help describes. Should be false. Any ideas?

 

[tvanstiphout]

Try this:


Public Sub testUpdateable()
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set td = db.TableDefs("Contacts")
Set fld = td.Fields("ContactID")

Debug.Print "Total Attributes: " & fld.Attributes
Debug.Print "Field is Autoincr: " & CStr((fld.Attributes And 16) = 16)
Debug.Print "Field is attribute Updateable: " & CStr((fld.Attributes And 32) = 32)
Debug.Print "Field is attribute Fixed: " & CStr((fld.Attributes And 1) = 1)
Debug.Print
Debug.Print "ContactID DataUpdatable: " & fld.DataUpdatable
End Sub

 

[jdraw]

MajP,
I tried the following based on your code. Note the error returned.

Public Sub TestAuto()

10        On Error GoTo TestAuto_Error
          Dim Rs As DAO.Recordset
          Dim fld As DAO.Field
20        Set Rs = CurrentDb.OpenRecordset("Select AutoID from tblTestAuto")
30        Set fld = Rs.Fields("AutoID")

40        Debug.Print "Total Attributes: " & Rs.Fields("AutoID").Attributes
50        Debug.Print "Field is Autoincr: " & CStr((Rs.Fields("AutoID").Attributes And 16) = 16)
60        Debug.Print "Field is attribute Updateable: " & CStr((Rs.Fields("AutoID").Attributes And 32) = 32)
70        Debug.Print "Field is attribute Fixed: " & CStr((Rs.Fields("AutoID").Attributes And 1) = 1)
80        Debug.Print
90        Debug.Print "AutoID DataUpdatable: " & Rs.Fields("AutoID").DataUpdatable
          Dim SQL As String
100       SQL = "Update tblTestAuto set autoId = AutoID +10;"
110       CurrentDb.Execute SQL, dbFailOnError
       
         
120       On Error GoTo 0
TestAuto_Exit:
130       Exit Sub

TestAuto_Error:

140       MsgBox "Error " & Err.Number & " (" & Err.Description & "), line " & Erl & " in Procedure TestAuto" _
              & "  Module  ZZ_ScratchPad "
          Debug.Print "Error " & Err.Number & " (" & Err.Description & "), line " & Erl & " in Procedure TestAuto"
150       GoTo TestAuto_Exit
End Sub

Debug output:

Total Attributes: 49
Field is Autoincr: True
Field is attribute Updateable: True
Field is attribute Fixed: True

AutoID DataUpdatable: True
Error 3113 (Cannot update 'autoId'; field not updateable.), line 110 in Procedure TestAuto

 

[MajP]

AutoID DataUpdatable: True
Error 3113 (Cannot update 'autoId'; field not updateable.), line 110 in Procedure TestAuto

That validates my point. The field is not updateable but the DataUpdatable property says that it updateable.

 

[jdraw]

I also adjusted Tom's code to use my table: tblTestAuto

' ----------------------------------------------------------------
' Procedure Name: testUpdateable
' Purpose: Testing Updateable attribute MajP and Tom
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 22-Jan-24
'
' SEE: https://www.access-programmers.co.uk/forums/threads/dao-dataupdatable-not-expected-or-not-understanding.330027/#post-1905969
' ----------------------------------------------------------------
Public Sub testUpdateable()
10        On Error GoTo testUpdateable_Error
      Dim db As DAO.Database
      Dim td As DAO.TableDef
      Dim fld As DAO.Field

20    Set db = CurrentDb
30    Set td = db.TableDefs("tblTestAuto")
40    Set fld = td.Fields("AutoID")

50    Debug.Print "Total Attributes: " & fld.Attributes
60    Debug.Print "Field is Autoincr: " & CStr((fld.Attributes And 16) = 16)
70    Debug.Print "Field is attribute Updateable: " & CStr((fld.Attributes And 32) = 32)
80    Debug.Print "Field is attribute Fixed: " & CStr((fld.Attributes And 1) = 1)
90    Debug.Print
100   Debug.Print "Check field AutoID DataUpdatable: " & fld.DataUpdatable
        Dim SQL As String
110       SQL = "Update tblTestAuto set autoId = AutoID +10;"
120       CurrentDb.Execute SQL, dbFailOnError
        
          
130       On Error GoTo 0
testUpdateable_Exit:
140       Exit Sub

testUpdateable_Error:

150       MsgBox "Error " & Err.Number & " (" & Err.Description & "), line " & Erl & " in Procedure testUpdateable" _
         & "  Module  ZZ_ScratchPad "
160      Debug.Print "Error " & Err.Number & " (" & Err.Description & "), line " & Erl & " in Procedure testUpdateable"
170       GoTo testUpdateable_Exit
End Sub

Here's the Debug Output:

Total Attributes: 17
Field is Autoincr: True
Field is attribute Updateable: False <<<<<<<<< Why?
Field is attribute Fixed: True

Check field AutoID DataUpdatable: False
Error 3113 (Cannot update 'autoId'; field not updateable.), line 120 in Procedure testUpdateable

 

[MajP]

You can use the DataUpdatable property on Field objects that are appended to the Fields collection of QueryDef, Recordset, and Relation objects, but not the Fields collection of Index or TableDef objects

As mentioned fields of Tabledefs are not supported. Every tabledef field will show as not updateable.

Same code one with RS and one with TDF

Public Sub Test()
  Dim Rs As DAO.Recordset
  Dim fld As DAO.Field
  Set Rs = CurrentDb.OpenRecordset("Select * from tblDemoUnbound")
  Set fld = Rs.Fields("PersonID")
  Debug.Print fld.Name
  Debug.Print "Total Attributes: " & fld.Attributes
  Debug.Print "Field is Autoincr: " & ((fld.Attributes And 16) = 16)
  Debug.Print "Field is attribute Updateable: " & ((fld.Attributes And 32) = 32)
  Debug.Print "Field is attribute Fixed: " & ((fld.Attributes And 1) = 1)
  Debug.Print
  Debug.Print fld.Name & " DataUpdatable: " & Rs.Fields("PersonID").DataUpdatable
  Debug.Print
End Sub
Public Sub Test2()
  Dim tdf As DAO.TableDef
  Dim fld As DAO.Field
  Dim db As Database
  Set db = CurrentDb
  Set tdf = db.TableDefs("tblDemoUnbound")
 
  Set fld = tdf.Fields("PersonID")
   Debug.Print fld.Name
  Debug.Print "Total Attributes: " & tdf.Fields("PersonID").Attributes
  Debug.Print "Field is Autoincr: " & ((fld.Attributes And 16) = 16)
  Debug.Print "Field is attribute Updateable: " & ((fld.Attributes And 32) = 32)
  Debug.Print "Field is attribute Fixed: " & ((fld.Attributes And 1) = 1)
  Debug.Print
  Debug.Print fld.Name & " DataUpdatable: " & fld.DataUpdatable
  Debug.Print
End Su

Results differ

RS
Total Attributes: 49
Field is Autoincr: True
Field is attribute Updateable: True
Field is attribute Fixed: True

PersonID DataUpdatable: True

TDF
Total Attributes: 17
Field is Autoincr: True
Field is attribute Updateable: False
Field is attribute Fixed: True

PersonID DataUpdatable: False

 

[jdraw]

Tom,

Can you explain this apparent difference? Somehow you chose the TableDef and Field route -- why- just curious.

MajP,
This "but not the Fields collection of Index or TableDef objects " seems to indicate DataUpdatable can not/should not be used with TableDef???

 

[tvanstiphout]

Try this:


Public Sub testUpdateable()
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set td = db.TableDefs("Contacts")
Set fld = td.Fields("ContactID")

Debug.Print "Total Attributes: " & fld.Attributes
Debug.Print "Field is Autoincr: " & CStr((fld.Attributes And 16) = 16)
Debug.Print "Field is attribute Updateable: " & CStr((fld.Attributes And 32) = 32)
Debug.Print "Field is attribute Fixed: " & CStr((fld.Attributes And 1) = 1)
Debug.Print
Debug.Print "ContactID DataUpdatable: " & fld.DataUpdatable
End Sub

My point is: go to the source = TableDef, not some Recordset construct on top of it.

 

[The_Doc_Man]

@MajP, your original post showed this:

  Set Rs = CurrentDb.OpenRecordset("Select PersonID from tblDemoUnbound")

which means you are opening a recordset to a non-stored query for which there is no formal (pre-existing) QueryDef. Therefore, I wonder just how well the field's properties in the table are passed through to that recordset when you actually open it.

I find it interesting that you aren't supposed to use the dbUpdatableField flag on members of the TableDef's Field object collection, yet that seems to work in Tom's test. I also note that the quotes claim it is OK to use dbUpdatableField when dealing with members of a QueryDef's Field object collection. Yet surely the QueryDef merely copies the Field elements from its contributing TableDefs.

You have, indeed, found a little puzzler.

 

[Edgar_]

In my tests, queries and recordsets show the property as True, in tableDefs the property is false even for non AutoIncrements. In fact, the attributes property shows 17 for tabledefs and 49 for queries and recordsets.

All of these fields are Field2 type

 

[MajP]

As far as I can tell neither work. Or at least I do not understand the results. I tested both an auto increment and an editable field using both the RS and TDF. Both give erroneous results.
TDF seems to be say all fields are not updateable, and RS seems to say that non-updateable auto increment fields are updateable.

Public Sub Test()
  Dim Rs As DAO.Recordset
  Dim fld As DAO.Field
  Set Rs = CurrentDb.OpenRecordset("Select * from tblDemoUnbound")
  Set fld = Rs.Fields("PersonID")
  Debug.Print fld.Name
  Debug.Print "Total Attributes: " & fld.Attributes
  Debug.Print "Field is Autoincr: " & ((fld.Attributes And 16) = 16)
  Debug.Print "Field is attribute Updateable: " & ((fld.Attributes And 32) = 32)
  Debug.Print "Field is attribute Fixed: " & ((fld.Attributes And 1) = 1)
  Debug.Print
  Debug.Print fld.Name & " DataUpdatable: " & Rs.Fields("PersonID").DataUpdatable
  Debug.Print
End Sub
Public Sub Test2()
  Dim tdf As DAO.TableDef
  Dim fld As DAO.Field
  Dim db As Database
  Set db = CurrentDb
  Set tdf = db.TableDefs("tblDemoUnbound")
  
  Set fld = tdf.Fields("PersonID")
   Debug.Print fld.Name
  Debug.Print "Total Attributes: " & tdf.Fields("PersonID").Attributes
  Debug.Print "Field is Autoincr: " & ((fld.Attributes And 16) = 16)
  Debug.Print "Field is attribute Updateable: " & ((fld.Attributes And 32) = 32)
  Debug.Print "Field is attribute Fixed: " & ((fld.Attributes And 1) = 1)
  Debug.Print
  Debug.Print fld.Name & " DataUpdatable: " & fld.DataUpdatable
  Debug.Print
End Sub

I get these mixed results

TEST ONE An updateable field
Phone
Total Attributes: 34
Field is Autoincr: False
Field is attribute Updateable: True
Field is attribute Fixed: False

Phone DataUpdatable: True

Phone
Total Attributes: 17
Field is Autoincr: False
Field is attribute Updateable: False "Wrong"
Field is attribute Fixed: False

Phone DataUpdatable: False "wrong"

Test Two Autonumber field
PersonID
Total Attributes: 49
Field is Autoincr: True
Field is attribute Updateable: True  "Wrong maybe"
Field is attribute Fixed: True

PersonID DataUpdatable: True  "Wrong"

PersonID
Total Attributes: 17
Field is Autoincr: True
Field is attribute Updateable: False
Field is attribute Fixed: True

PersonID DataUpdatable: False

 

[The_Doc_Man]

Now I'm even more intrigued. Why is this expression CStr((fld.Attributes And 32) = 32) returning TRUE or FALSE when in fact it should be returning -1 or 0 ?

Because parenthetical expression fld.Attributes AND 32 will return either 0 or 32. Then remove the innermost parentheses and evaluate previous-parenthetical-expression = 32 which will return either 0 or -1. And finally, CStr of any number based on a numeric expression should convert the resultant number to a numeric string.

OK, I get that there is a TRUE/FALSE situation here and therefore a TRUE/FALSE answer is appropriate - but I don't think I understand the semantics that led to this result.

 

[MajP]

Now I'm even more intrigued. Why is this expression CStr((fld.Attributes And 32) = 32) returning TRUE or FALSE when in fact it should be returning -1 or 0 ?

In the immediate window any boolean logic/variable is shown with a True/False, Also I got rid of the CSTR since not needed.
?(1=12)
False

 

[The_Doc_Man]

I understand getting rid of the CSTR. I have a few experiments to perform, though.

 

[Josef P.]

Note: DataUpdatable depends on the RecordsetType.

dbOpenDynaset      True
dbOpenSnapshot     False
dbOpenForwardOnly  False
dbOpenTable        True

This is still somewhat logical, since dbOpenSnapshot and dbOpenForwardOnly do not allow editing.

But: DataUpdatable=True is even output for a calculated field.

Has an ACE developer made it easy for himself and simply passed on the value of the recordset property ' Updatable ' to the fields?

The output is correct for calculated fields within the SQL statement:

With CurrentDb.OpenRecordset("select Id, Id+1-1 as CalcId from msysAccessStorage", dbOpenDynaset)
    Debug.Print "Recordset.Updatable = "; .Updatable
    For Each fld In .Fields
        Debug.Print fld.Name; ": "; fld.DataUpdatable, fld.Attributes
    Next
    .Close
End With
' =>
' Recordset.Updatable = True
' Id:     True  49
' CalcId: False  1

 

[gemma-the-husky]

According to my DAO object model updateable for a table Def object is true, but false for a linked table. I think it refers to the tabledef itself, not the data it contains. The data is always updateable isn't it, logically.

 

[MajP]

According to my DAO object model updateable for a table Def object is true, but false for a linked table. I think it refers to the tabledef itself, not the data it contains. The data is always updateable isn't it, logically.

That is not what it appears to say, and not consistent between a RS and TDF

Use this property to determine whether you can change the Value property setting of a Field object. This property is always False on a Field object whose Attributes property is dbAutoIncrField

The RS seems to return that all fields are updateable and TDF returns all fields are not. So I do not understand how this property works.

Public Sub Test3()
  Dim Rs As dao.Recordset
  Dim fld As dao.Field
  Set Rs = CurrentDb.OpenRecordset("tblDemoUnbound", dbOpenDynaset)
  For Each fld In Rs.Fields
    Debug.Print fld.Name & " is updateable: " & fld.DataUpdatable
  Next fld
  Debug.Print
  Dim tdf As dao.TableDef
  Dim db As Database
  Set db = CurrentDb
  Set tdf = db.TableDefs("tblDemoUnbound")
    For Each fld In tdf.Fields
    Debug.Print fld.Name & " is updateable: " & fld.DataUpdatable
  Next fld

End Sub

PersonID is updateable: True
First_Name is updateable: True
Last_Name is updateable: True
Email is updateable: True
Phone is updateable: True
DateField is updateable: True
LongField is updateable: True
DoubleField is updateable: True
CurrencyField is updateable: True

PersonID is updateable: False
First_Name is updateable: False
Last_Name is updateable: False
Email is updateable: False
Phone is updateable: False
DateField is updateable: False
LongField is updateable: False
DoubleField is updateable: False
CurrencyField is updateable: False

 

[gemma-the-husky]

My book says for a record set, it determines whether the rs is updateable or not, and we know some rs are non updateable.ie the Data cannot be edited.

To me it's saying whether the tabledef structure itself is updateable, ie, generally whether it's a linked table or not, so you can't edit a linked table. It's not talking about the data in the table.

Is your table tblDemoUnbound linked or local?

 

[MajP]

My book says for a record set, it determines whether the rs is updateable or not, and we know some rs are non updateable.ie the Data cannot be edited.

To me it's saying whether the tabledef structure itself is updateable, ie, generally whether it's a linked table or not, so you can't edit a linked table. It's not talking about the data in the table.

Is your table tblDemoUnbound linked or local?

That does not really make sense, but MS does not always make sense. Yes a recordset also has an updateable property and that would tell if the recordset is updateable.
However according to MS this is a field property and should tell if the field is updateable even if the recordset is 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.

And again they discuss a field here

Use this property to determine whether you can change the Value property setting of a Field object. This property is always False on a Field object whose Attributes property is dbAutoIncrField

Contrary to what @tvanstiphout said, this does not work with a tabldef. In fact it never works. Every field is always shown as non updateable. So Tabledefs should not be part of this discussion.

ou can use the DataUpdatable property on Field objects that are appended to the Fields collection of QueryDef, Recordset, and Relation objects,

but not the Fields collection of Index or TableDef objectsI

Old thread with the same question and no resolution.

DataUpdateable DOES NOT WORK!!!! - vbCity - The .NET Developer Community

vbCity is a community of VB and .NET developers joined together with a common goal: to learn, teach, and have fun programming. Developers from all over the world come together to share knowledge, source code, and tutorials for free to help their fellow programmers - Professional Developers...

vbcity.com

With that said it sure seems that this property at the field level only returns if the recordset is or is not updateable.

 

[gemma-the-husky]

Maybe they could have used a different property name to distinguish between updateable in the sense of adding or modifying stored data, and modifiable in the sense of editing the structure.

To me the tabledefs are to do with the structure/thema of the database, and not to with the data stored in those objects. I would never think to examine an updateable property of a tabledef.