HOWTO: Uniquely Identify the Same Name Fields from N Tables (191808)

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.

MORE 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=<Server Name>;" & _
      "Initial Catalog=Northwind;" & _
      "User ID=<username>;PASSWORD=<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.