Recordset and referencing a field [Resolved]

[Maldrid]

I am using an ADO recordset. Now there are several ways to reference a field within the recordset. Is there any difference in the way you do it? Is one way better than the other?

rs.fields("Field 1")
rs("Field 1")
 
rs(0) 'I think this way would be the fastest since it doesn't
'have to do any comparison of the names in the fields. It knows
'to go straight to the first field in the recordset
 
'there is one more way using the ! but I forget the syntax

I was just wondering if any method is better.

[Briantcva]

Generally speaking you don't want to view fields by index number - makes your code much less readable and could potentially break it/render it useless should the field order change. Your first two examples are the same, the field property is just the default property of the rs object - as .text is to the textbox control. The other method is

rs!Field1

[Briantcva]

From one of my MS training texts:

"The most efficient technique is to reference the field name directly. You can use the Fields collection, which is more explicit code, but is less efficient."

'most efficient
txtFirstName.Text = rs!First_Name
 
'explicit, but less efficient
txtFirstName.Text = rs.Fields("First_Name").Value

[Maldrid]

There is no way of using the more efficient way if you have spaces in your fields is there?

'This can not be done since there is a space
rs!Field Name
'Obviously this can't be done either since syntax is wrong
rs!"Field Name"

The only way I could fix this is if I re-name the fields using underscores instead of spaces.

Thanks for your replies, I will note it in the future.

[techgnome]

Originally posted by Maldrid
There is no way of using the more efficient way if you have spaces in your fields is there?

'This can not be done since there is a space
rs!Field Name
'Obviously this can't be done either since syntax is wrong
rs!"Field Name"

The only way I could fix this is if I re-name the fields using underscores instead of spaces.

Thanks for your replies, I will note it in the future.

'This can not be done since there is a space
rs!Field Name
'Obviously this can't be done either since syntax is wrong
rs!"Field Name"
 
'But this works....
rs![Field Name]

I usually use the .Fields("[Field Name]").value method....
that way if the fields get moved aorund in the query (happens quite often)... then things don't get hoarked .... as would happen if I used the .Fields(intFldNum).Value....

[Maldrid]

Ah ok, thats great. Thanks!