Solved

Database Fields

Posted on 2002-06-14
4
136 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 143

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

856 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