VB6 / ADO / MS Sql Server field name resolution.

Using ADO to connect to MS Sql Server from VB6 we access the names of fields in the following manner.

FieldName = rs.Fields(r&).Name

In our SQL queries we sometimes need to implicitly name the table with the field name especially where joins are concerned see example...

Select Person.FullName, Person.FirstName from Person etc....

The problem is that the table name is not returned in the fieldname as it is with many of the other backend databases that we are familiar with.

Is it possible to get the source table for each field in the recordset?

finsterAsked:
Who is Participating?
 
amebaCommented:
No, it gives "BASETABLENAME" :)
Use .Value
0
 
amebaCommented:
>get the source table
rs.Fields(1).SourceTable
0
 
finsterAuthor Commented:
Which version of ADO is that? I cannot find it in the object library, and I know that this functioinality was in DAO and RDO.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
MarineCommented:
field1 = rs(0).name

to get a value

just simply this

field2 = rs(0)

i don't really understand the question of what you need to be done.
0
 
finsterAuthor Commented:
In the sample query:

Select Person.FullName, Person.FirstName from Person

we need an ADO method that will return "Person" when queried. We can already get the FieldName, but we also want the Table Name of where that field came from.
0
 
amebaCommented:
Sorry, that 'SourceTable' was DAO.
You can check properties collection of your field, e.g.
? rs.fields(1).Properties(1).name
BASETABLENAME

Depending on the provider, that property can have other name.
After checking property name, use something like
? rs.fields(1).Properties("BASETABLENAME").value
0
 
vinoopaulsCommented:
as ameba mentioned

rs.fields(1).Properties(1).name

gives the table name
 
0
 
finsterAuthor Commented:
for this method, we found that

rs.fields(1).Properties(3).value

gave us what we wanted

Thanks to all
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.