How to find full field name in ADODB ?

[Michel S.]

Hi !

Given a recordset build from an SQL query containing fields from many
tables.

Getting or setting the value of the resulting recordset fields is non
trivial because sometimes the field "name" property in in the form
"sourcetable.fieldname" and sometimes "fieldname" only.

With DAO, we can always resort to the SourceTable and SourceField
properties to positively identify a field, but as far a I know, there's
no equivalent of this in ADODB.

My questions :

1) Is it true that there's no equivalent of SourceTable / SourceField
properties in ADODB ?

2) Is there a way to get all field names of and SQL query to be
returned in a fully qualified "sourcetable.sourcefield" form - either
in DAO or in ADODB ?

Thanks !

 

[Ken Snell \(MVP\)]

If you're building the SQL statement, then you can control the names that
represent the individual fields, either by using aliases (best approach) or
by fully qualifying the name with the source table or query. I recommend
using the alias approach.

 

[Michel S.]

Thanks for your answer..

Do I understand the two possible ways you suggest require to completely
avoid "SELECT * FROM" and list *every* field in the SELECT part of the
query, either by using a SELECT T1F1=Table1.Field1,
T1F2=Table1.Field2... format or SELECT Table1.Field1 AS T1F1....
(your recommended approach) or SELECT Table1.Field1, Table1.Field2 ?



Ken Snell (MVP) a écrit :

 

[Ken Snell \(MVP\)]

Yes, that is what I recommend. Note that using the * is not disallowed for
what you seek to do, but you then must be careful about how you "reference"
the field and tables in your use of the name. For example, if the query
returns the Name1 field from Table1 and Table2, you can reference the field
from Table1 this way in many cases:

[Table1.Name1]

I've not tried the above syntax if the field or table name contains spaces
or other "not desired" characters, so I don't know if

[Table 1.Name 1]

would work for a field named Name 1 and a table named Table 1.

 

[Michel S.]

Thanks again for your reply Ken..

I'm just curious: if the "state of the art" suggest to avoid "SELECT *"
and qualify each field instead, does it mean that a DBA have to review
all queries/stored procedures every time a change is made to a table in
the DB to make sure the field is included in the returned recordset
(what a SELECT * avoid) ?

In other words, there's no SQL keyword to tell "return a full qualified
field name" for all fields in the select statement ?

Is this a flaw in the SQL syntax or just an unsatistied developper
dream ?




Ken Snell (MVP) a couché sur son écran :

Yes, that is what I recommend. Note that using the * is not disallowed for
what you seek to do, but you then must be careful about how you "reference"
the field and tables in your use of the name. For example, if the query
returns the Name1 field from Table1 and Table2, you can reference the field
from Table1 this way in many cases:

[Table1.Name1]

I've not tried the above syntax if the field or table name contains spaces or
other "not desired" characters, so I don't know if

[Table 1.Name 1]

would work for a field named Name 1 and a table named Table 1.

--

Ken Snell
<MS ACCESS MVP>

Thanks for your answer..

Do I understand the two possible ways you suggest require to completely
avoid "SELECT * FROM" and list *every* field in the SELECT part of the
query, either by using a SELECT T1F1=Table1.Field1, T1F2=Table1.Field2...
format or SELECT Table1.Field1 AS T1F1.... (your recommended approach) or
SELECT Table1.Field1, Table1.Field2 ?

Ken Snell (MVP) a écrit :

 

[Ken Snell \(MVP\)]

Your question, "there's no SQL keyword to tell "return a full qualified
field name" for all fields in the select statement ?" goes beyond my SQL
expertise, sorry. I do not know if there is such a syntax or not; note that
the answer may be dependent upon which SQL dialect you're using.

I suggest that you post this question in a new post in the
microsoft.public.access.queries newsgroup -- that may get you an answer from
another, more qualified person who sees the post.

 

[Terry Kreft]

Using ADO

Consider the following code

Dim r As ADODB.Recordset

Const strSQl = "SELECT h.ord_num, d.ord_line as [Line No] " _
& "FROM order_header h " _
& "INNER JOIN order_detail d " _
& "ON h.ord_num = d.ord_num"

Set c = Application.CurrentProject.Connection

Set r = c.Execute(strSQl)

Debug.Print "Field name in recordset = "; r.Fields(0).Name
Debug.Print "Field name in database = ";
r.Fields(0).Properties("BASECOLUMNNAME")
Debug.Print "Table name in database = ";
r.Fields(0).Properties("BASETABLENAME")
Debug.Print
Debug.Print "Field name in recordset = "; r.Fields(1).Name
Debug.Print "Field name in database = ";
r.Fields(1).Properties("BASECOLUMNNAME")
Debug.Print "Table name in database = ";
r.Fields(1).Properties("BASETABLENAME")

r.Close
Set r = Nothing
Set c = Nothing


This produces
Field name in recordset = ord_num
Field name in database = ord_num
Table name in database = order_header

Field name in recordset = Line No
Field name in database = ord_line
Table name in database = order_detail


So in answer to your questions
1) No it's not true

2) Yes.