Solved

Database Fields

Posted on 2002-06-14
4
134 Views
Last Modified: 2013-11-26
I have seen both the following methods used to retrieve a database field

Method 1 Example

cboStatus.AddItem oRs.Fields("ProjectName")

Method 2 Example

cboStatus.AddItem oRs!Status

What is the difference between the two methods and is one better than the other?


0
Comment
Question by:mutrus
4 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 50 total points
ID: 7077819
Method 2 is only supported for backwords compatiblity.

The BEST way would be like this:

dim oRs as Recordset  'not sure if you speak ADO or DAO...
dim fStatus as Field

set oRs = new Recordset
oRs.Open ...

set fStatus = oRs.Fields("Status")
cboStatus.Visible = false

while not oRs.eof
  cboStatus.AddItem fStatus.Value
  oRs.MoveNext
wend

cboStatus.Visible = true

set fStatus = nothing
oRs.Close
set oRs = nothing

CHeers
0
 
LVL 2

Expert Comment

by:RSivakumar_Sofil
ID: 7077858
Method 1
--------
To be honest, you do save a few keystrokes, but that's about it. The syntax itself is "old school". Additionally, using this syntax embeds literals in your code. Change a field name, and you have to change every reference to that field. Misspell the field name, and you have an error (nb. These same problems potentially apply to using the field name as a key, but that method allows you a way to avoid the problem, where this one doesn't).

Method 2
--------
Although using the Fields collection with the field name as a key offers no performance advantage over the first syntax, it is more in line with standard syntax. For that reason, this is more readable as well. As noted above, using this method could lead to maintenance problems if you use literals for your field names. The fix for that, however, is to use meaningful constants instead of literals. That way, any changes to a field name (or spelling errors) can be corrected in one location. The compiler will handle ensuring that the spelling of the constants is correct (assuming you are using 'Option Explicit').

cheers.
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 7077863
I agree with angelIII, as a matter of fact when you use the ! (bang) operator VB has to perform an extra step in its processing, internally converting your code from oRS!Status to oRs.Fields("Status").Value before executing the statement. Whilst this is a very small step and does not take a significant amount of processing in a very high level transaction processing environment it could have a slight effect on speed. Personally I would always use oRs.Fields("Status").Value, as it is more flexible in that you can use variables as your field name:

Dim strFielName As String
Dim i As Integer
For i = 0 To .Fields.Count - 1
  Debug.Print .Fields("MyField" & CStr(i)).Value
Next
0
 

Author Comment

by:mutrus
ID: 7079841
All answers were appropriate and gave me the information I require. angellll was first.

Thanks to all
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now