The reason this particular code started breaking is because of the introduction of a new DAO field type (
Field3) in Access version 2206. There are now three different types of DAO fields:
DAO.Field: the original field object
DAO.Field2: extended the original to include support for MVFs
DAO.Field3: extended Field2 to include support for Precision and Scale properties of Decimal types
Why Are There Multiple DAO Field Types?
For this explanation, let's go to the Microsoft response that Access MVP Gustav Brock relayed in his SO answer:
The simplest workaround for now is to change declarations of type
Field2 was added to support properties for multi-valued fields, Field3 was added to support the Precision and Scale properties for decimal types. (You’ll note that there is also a Recordset2 object in the browser, for similar reasons).
You don’t get intellisense for the extended properties if you declare as ‘Field’, but you can still access all the properties in code.
When we make an update to an interface, we can’t update the existing interface, but must extend the existing interface. We are looking into whether we can improve the backward compatibility issue for cases where people explicitly declared variables to be of type Field2, rather than just Field.
What Should You Do About This?
The bottom line is that you should NEVER declare a DAO Field variable as anything other than
Any code that declares variables as
DAO.Field3, should be immediately changed to
DAO.Field. Likewise for any code with variables declared as
DAO.Recordset2; those should be changed to
DAO.Recordset for the same reasons.
By doing this, you will ensure that your code will continue to run when Microsoft inevitably introduces
Recordset4 types in the future.
If switching to DAO.Field works, I would also try switching from DAO.Recordset2 to DAO.Recordset.
Also no errors in this test case.
Caveats and Additional Notes
DAO. Prefix Is Optional
Your code may not include the
DAO. prefix before your
The prefix is optional but highly encouraged because the ADO library also has Field and Recordset objects. If you include references to both DAO and ADO in your Access application, then a bare
Dim rs As Recordset declaration will choose DAO or ADO based on the order you set in Tools > References. You can override that behavior by declaring objects as
It does not appear that ADO has a Field2 or Recordset2 type. That means that if you have both ADO and DAO referenced in your project and ADO has higher priority, then switching
As Field2 to
As Field will have the unintended side effect of changing that field variable from DAO to ADO.
When you switch from
As Field2 to
As Field, you will lose the following properties and methods from the IntelliSense for your Field object:
It is important to note, as Microsoft did in their response at the top of this article, that you will still be able to use the above properties and methods in your code at runtime. You just lose the IntelliSense.
When you switch from
As Recordset2 to
As Recordset, you will lose the following properties entirely from your Recordset object (not just from IntelliSense):
Field object, if you need to reference the
ParentRecordset property of the
Recordset2 object, you will need to declare your Recordset2 object as a generic object. For code readability, I would recommend the following declaration:
Dim rs As Object 'DAO.Recordset2
However, you should only take the above approach if you need to use the ParentRecordset property of the Recordset2 object. I have no idea why the behavior is different between the Field/Field2 objects and the Recordset/Recordset2 objects.
If you really want the IntelliSense, you can take a page from the late binding/early binding playbook and have two declarations: one that you use during development and a different one that you use when you deploy your code.
'Uncomment the top line for development; ' uncomment the bottom line for production 'Note that you may need to adjust the top line as new ' versions of Recordset and Field are released 'Dim rs as DAO.Recordset2, fld as DAO.Field2 Dim rs As DAO.Recordset, fld As DAO.Field For Each fld in rs.Fields ... Next