Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Database Fields

Posted on 2002-06-14
4
Medium Priority
?
145 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 200 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
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…
Suggested Courses

670 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