CODEWITHSUNDEEP
ms-accessvba
acr_scout
acr_scout

Reputation: 579

What are difference between referencing a DAO Recordset field?

I am curious what are the differences between the following and if there are advantages for one over the other.

Assume MS Access and DAO Recordset

rst![field]
rst.Fields("field")

Upvotes: 2

Views: 388

Answers (1)

HansUp
HansUp

Reputation: 97101

rst![field] refers to an item named field in the recordset's default collection. The Fields collection is the recordset's default collection. So ! allows you to reference the field without explicitly saying you want a Field.

From what I've read in the past, ! reference is the fastest way to access a collection member. I'm not really sure about the magnitude of the performance impact; I use ! because it's more concise (compared to rst.Fields("field")).

And if you will be accessing more than one of the recordset fields, consider a With block:

With rst
    Debug.Print !ID.Value
    Debug.Print !Last_Name.Value
End With

Supposedly that can be even faster because Access doesn't have to start from rst again each time to get to the target field. Again I'm unsure how much performance improvement that offers; I like it when it makes code more concise.

rst.Fields(<field name>) is useful when you don't know the field name ahead of time. In those cases, use a string variable to hold the field name and feed that variable to rst.Fields. But I don't see any benefit with rst.Fields and a hard-coded field name. That's just more typing for no gain.

As a side point, consider what rst![field] actually gives you. Generally you want .Value, which is the field's default property, so you don't need to explicitly ask for .Value in those situations. However in a situation like adding a recordset field to a VBA Collection, the actual field object rather than its .Value gets added to the collection. That can be an unwelcome surprise! Gord Thompson convinced me to always include .Value when I want .Value, so that's what I try to do.

Upvotes: 3

Related Questions