Microsoft KB Archive/191808

= HOWTO: Uniquely Identify the Same Name Fields from N Tables =

Article ID: 191808

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q191808



SUMMARY
A query against tables having columns with the same name results in a recordset with duplicate field names. This makes it difficult to identify the parent table of the fields with the same name.

For example:   TABLE1            TABLE2 ---          ---   col               col If the statement "select * from table1, table2" is issued, there will be two fields in the resulting recordset with the name of "col". ActiveX Data Objects (ADO) does not rename any columns. To workaround this, alias the column names as indicated in the following example: select Table1.col as A table2.col as B from a, b If you do not alias the column names you can use the field property BASETABLENAME to determine the parent tablename.

Note:Dynamic field properties are not set for forward only read-only cursors.

The following code prints the basetable name: rs.open "...",cn,adOpenKeyset debug.print rs(0).properties("BASETABLENAME") Getting the information about the BASETABLENAME is an expensive proposition and many backends do not readily provide this information.



Steps to Reproduce Behavior
This sample uses the Northwind database that comes with Microsoft SQL Server 7.0.  Start a new Visual Basic Standard EXE project and reference the Microsoft ActiveX Object Library.  Paste the following code in the Form_Load event: Dim cn as new connection Dim rs as new recordset Dim i as integer

cn.Open "Provider=SQLOLEDB;" & _ "Data Source=;" & _ "Initial Catalog=Northwind;" & _ "User ID= ;PASSWORD= "

rs.ActiveConnection = cn     rs.CursorType = adOpenStatic

rs.Open "select customers.companyname, shippers.companyname " & _ "from customers, orders, shippers " & _ "where customers.customerid=orders.customerid and " & _ "orders.shipvia=shippers.shipperid"

For i = 0 To rs.Fields.Count - 1 Debug.Print rs(i).Name Next The preceding code results in two fields with the name companyname. Modify the select to use aliases. For example: rs.open "select a.companyname As CustomersCompany, " & _ "b.companyname As ShippersCompany " & _ "from customers a, orders c, shippers b " & _ "where a.customerid=c.customerid and c.shipvia=b.shipperid"



Now column names appear as CustomersCompany and ShippersCompany.

If it is preferable not to use aliases then you can use the following code to identify the parent table: Debug.print rs(i).properties("BASETABLENAME") & "" However, obtaining BaseTableName is an expensive operation, is only supported for serverside recordsets, and is not supported by all providers.

Keywords: kbhowto kbdatabase KB191808

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.