?
Solved

Determine if Field Exists in Recordset

Posted on 2003-03-16
9
Medium Priority
?
458 Views
Last Modified: 2008-02-01
How can I determine if a field is part of a recordset?

I don't want to loop through the Fields collection, or use ON ERROR.

Example:

rstMyTable.open "SELECT * FROM MyTable", objConn

If the field "Myfield" does not exist:

IsObject(rstMyTable.Fields("Myfield"))     'returns error 3265
IsNull(rstMyTable("Myfield"))               'returns error 3265

Any ideas?
0
Comment
Question by:apollois
[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
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8148686
Why are you unable to loop through the collection or error trap ?

Also, under what circumstances do you need to test for the field's existence ?
0
 
LVL 10

Author Comment

by:apollois
ID: 8148731
shanesuebsahakarn,

>>>Why are you unable to loop through the collection or error trap ?

Constraints given to me.


>>>Also, under what circumstances do you need to test for the field's existence ?

I have a custom DLL which returns a recordset.  It returns different fields depending on the criteria.  I do not have access to the DLL or it's source code.




Best Regards,
>apollois<
0
 
LVL 3

Expert Comment

by:Moliere
ID: 8149169
Use:
ON ERROR RESUME NEXT
then assign isnull(rstMyTable.fields("Myfield")) to a variable.
Check for an error 3265. If it is there, then the field does not exist. If the ERR=0, then the field exists.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:Moliere
ID: 8149178
Sorry, I missed the part about not using ON ERROR
0
 
LVL 10

Author Comment

by:apollois
ID: 8149184
Moliere,

Please re-read my question:
I don't want to loop through the Fields collection, or use ON ERROR.


Best Regards,
>apollois<
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 500 total points
ID: 8149270
Hmm, well under these circumstances, I know of no way that it can be done.

Neither IsObject or IsNull will work since both of these require valid Variant references in the first place.

However, I am not really conversant enough with ADO. Conceivably, the line:
rstMyTable.open "SELECT " & MyField & " FROM MyTable", objConn

might return something you could check for, but I suspect that it would be a parameter error which you would need to trap.
0
 
LVL 1

Expert Comment

by:Hermetic
ID: 8149301
I am not sure of your situation, but if you are trying to determine which of two nearly identical tables you are selecting from, save MyField, then you could possibly use a count of the fields to tell if it is there.
0
 
LVL 6

Expert Comment

by:DrTech
ID: 8151006
I don't see any reason NOT to loop through the fields collection. It takes almost no time, and is the only solution I can think of.
0
 
LVL 10

Author Comment

by:apollois
ID: 8157576
I think you're right.  The only options are check the Fields collection or use ON ERROR.

This is what I thought, but others were insisting there must be another way.

Thanks to all for confirming this.  Shane was first, so he gets the points.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

765 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