HOWTO: Uniquely Identify the Same Name Fields from N Tables

ID: Q191808

The information in this article applies to:

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

  1. Start a new Visual Basic Standard EXE project and reference the Microsoft ActiveX Object Library.


  2. 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=<user name>;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: 
          select a.companyname 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")


REFERENCES

  1. Connect to Support Online at the following Internet address:


    2. http://support.microsoft.com/support/default.asp
  2. In the My Question Is About box, select All Products.


  3. In the My Question Is box, type the Article ID (qnumber) of the article that you want to see. For example, type "Q162192" (without the quotation marks).


  4. Click Find (next to the My Question Is box). This search will return from one to several article titles. The Article ID appears in the upper-left corner of the article. You can see the Article ID only after you click to view the article.


  5. Click the title of the article to view it.


Additional query words: 


Keywords          : kbADO kbADO150 kbADO200 kbDatabase kbCodeSam 
Version           : WINDOWS:1.5,2.0
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: June 7, 1999