Solved

Database Fields

Posted on 2002-06-14
4
139 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

733 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