WARNING: Do NOT Declare DAO Fields As Field2, Field3, etc.

Over the past few days, there have been several bug reports where the following code breaks:

Dim rs as DAO.Recordset2
Dim fld as DAO.Field2

For Each fld in rs.Fields   '<- Code will break here, because Fields suddenly return Field3
   ... 
Next

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 DAO.Field2 to DAO.Field.

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 DAO.Field.

Any code that declares variables as DAO.Field2 or 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 Field4, Field5, Recordset3, Recordset4 types in the future.

Over on Adam Waller's excellent Access source control Github project, Github user Alan Van Art graciously tested this theory out for us:

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

The DAO. Prefix Is Optional

Your code may not include the DAO. prefix before your Field and Recordset declarations.  

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 DAO.Recordset or ADODB.Recordset accordingly.

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.  

Missing IntelliSense

When you switch from As Field2 to As Field, you will lose the following properties and methods from the IntelliSense for your Field object:

• AppendOnly
• ComplexType
• Expression
• IsComplex
• LoadFromFile
• SaveToFile

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):

• ParentRecordset

Unlike the 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.

IntelliSense Workaround

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.  

For example:

'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

External references