Friday, 22 June 2012

10. Nested recordsets in VBA

Access 2007 introduced the possibility that a field in a recordset may itself be a recordset. This applies to complex data types - multi-value fields and attachments - in an ACCDB, not MDB.

Any code that examines the Fields of a recordset or applies criteria is affected. Test the field's Type, and introduce another loop to walk the fields within the recordset that is a field in your main recordset.  
Many professional developers avoid the complex data types, believing they introduce more problems than they solve. The hidden structure makes it harder to manage them, harder to apply criteria, harder to pass arguments, harder to determine the delimiters to use for a field, and harder to upsize since other databases like SQL Server don't use these complex types. Eschewing the complex data types is a perfectly value choice if you only deal with databases you created, but if you support end users or write generic utilities to work with any Access tables, you must learn to handle them.


Specifically test for and handle the complex data types if your code must work with databases in Access 2007 or later. Particularly, if you:
  • Loop through the fields of a recordset, examining each.
  • Apply criteria to a field without knowing its data type.

No comments:

Post a Comment