INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Teradata Field names and DAO/ADO/OLEDB access problem

Teradata Field names and DAO/ADO/OLEDB access problem

(OP)
Here's the issue:
When accessing Teradata from MS Access 2007/XP/32bit, let's say I have a table called WELL. In that table, the first (and key) field is Well_Id.

Using DAO, I can say, rs!Well_ID or rs.Fields("Well_Id") and return the well id.
Using ADO, if there's no CAPTION, I can do the same. HOWEVER, if there *IS* a caption, I MUST use that caption.
So, let's say the caption is "Well Identifier"). Now, if I use rs!Well_Id, the statement fails. If I use rs.fields("Well_Id") the statement fails.
Only if I use rs.Fields("Well Identifier") does the statement succeed.
So - the answer is to remove all captions. Ok, fair enough, but not possible on tables that I'm linking to where other programs depend on the caption.
So, where there is a caption, I use that; where there isn't a caption, I use the field ID.

Using TDOLEDB, the problem is worse.
I can no longer say rs!Well_Id nor rs.fields("Well_Id"), I must say rs.fields("Well Id") as Teradata removes the underscores.
I have not tried tables with captions as the field ID problem, alone. presents a severe problem.

My only solution thus far is to write a program that creates CONST variables of the BYTE type that resolve to the absolute field position. And because Well_Id is used in several related tables, I must include the Table name.

ie.
Const tWellfWell_Id as Integer = 0

Then, I can use rs.fields(tWellfWell_Id) to return a value. Obviously, if a table is reorganized, I'd need to create a new constants list - no big deal. And since I'm using the table name and field name, the code is still readable. (using the actual number, like rs.fields(0) is NOT reasonable as mistakes WILL be made)

Has anyone found a way around this problem, such as a connection parameter, that I haven't found?

Any other suggestions?

Thanks,
Larry Wilson
ConocoPhillips, Houston

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close