Select Tables that have the same attribute (column)

[sloney]

I am trying to build relationships between mulitple tables and was wondering if there is a way to determine all tables in a database that contain the same attribute. Thanks in advance for your thoughts!

Sloney

[NeoPa]

If by attribute you are referring to a field, then you may well be able to.

The oft-overlooked Documentation (Tools / Analyse / Documenter) feature should help.

Use it to produce an RTF document of all the tables and their fields from the whole database, then save it and load it up into something like Word.

From there you can search for the field name and see where it turns up.

Clearly there are many more uses you can put this to. Have a play.

[NeoPa]

I suppose a more standard way might be helpful too (if you want to manage it in code).

The following code entered in as a single line will run in the Immediate Pane (Ctrl-G from the VBA Editor).

For Each tbl in CurrentDb.TableDefs : ?tbl.Name : Next tbl

It produces a list of all the tables in your database.

Strangely, the following code worked to produce a list of fieldnames for each field in a table. Any use of object variables though, produced errors. No idea why I'm afraid.

Public Sub ShowFields(strTable As String)
    Dim intIx As Integer
 
    For intIx = 0 To CurrentDb.TableDefs(strTable).Fields.Count - 1
        Debug.Print CurrentDb.TableDefs(strTable).Fields(intIx).Name
    Next intIx
End Sub

The following, for instance, should be equivalent and work exactly the same. It didn't for me :(

Public Sub ShowFields(strTable As String)
    Dim intIx As Integer
 
    With CurrentDb.TableDefs(strTable)
        For intIx = 0 To .Fields.Count - 1
            Debug.Print .Fields(intIx).Name
        Next intIx
    End With
End Sub

[ADezii]

If you are looking for perspective Fields, in multiple Tables, to see which ones are good candidates for potential Relationships, you should have a concise and comprehensive listing of all your Tables, all Field Names contained in those Tables, and very importantly their Field Types. I wrote a little routine which hopefully will provide this for you. Any questions, feel free to fire away. Place the Public Function in a Standard Code Module, and execute the Main Code from any location in the Database.

1. Main Code Block:
   

   Dim tdf As DAO.TableDef
   Dim fld As DAO.Field
    
   For Each tdf In CurrentDb.TableDefs
     If Left$(tdf.Name, 4) Like "*Sys" Then
       'Ignore System Tables
     Else
       Debug.Print tdf.Name
         For Each fld In tdf.Fields
           Debug.Print "  |-- [" & fld.Name & "] <==> {" & _
                          fReturnFieldType(fld) & "}"
         Next
     End If
   Next

2. Function Procedure:
   

   Public Function fReturnFieldType(fldRet As DAO.Field)
   Dim strReturn As String
    
   Select Case fldRet.Type
     Case dbBoolean: strReturn = "Yes/No"
     Case dbByte: strReturn = "Byte"
     Case dbInteger: strReturn = "Integer"
     Case dbLong
       If (fldRet.Attributes And dbAutoIncrField) = 0& Then
         strReturn = "Long Integer"
       Else
         strReturn = "AutoNumber"
       End If
     Case dbCurrency: strReturn = "Currency"
     Case dbSingle: strReturn = "Single"
     Case dbDouble: strReturn = "Double"
     Case dbDate: strReturn = "Date/Time"
     Case dbBinary: strReturn = "Binary"
     Case dbText
       If (fldRet.Attributes And dbFixedField) = 0& Then
         strReturn = "Text"
       Else
         strReturn = "Text [Fixed Width]"
       End If
     Case dbMemo
       If (fldRet.Attributes And dbHyperlinkField) = 0& Then
         strReturn = "Memo"
       Else
         strReturn = "Hyperlink"
       End If
     Case dbLongBinary: strReturn = "OLE Object"
     Case dbGUID: strReturn = "GUID"
     Case dbDecimal: strReturn = "Decimal"
     Case Else: strReturn = "Unknown"
   End Select

3. Sample OUTPUT:
   

   Categories
     |-- [CategoryID] <==> {AutoNumber}
     |-- [CategoryName] <==> {Text}
     |-- [Description] <==> {Memo}
     |-- [Picture] <==> {OLE Object}
   Customers
     |-- [CustomerID] <==> {Text}
     |-- [CompanyName] <==> {Text}
     |-- [ContactName] <==> {Text}
     |-- [ContactTitle] <==> {Text}
     |-- [Address] <==> {Text}
     |-- [City] <==> {Text}
     |-- [Region] <==> {Text}
     |-- [PostalCode] <==> {Text}
     |-- [Country] <==> {Text}
     |-- [Phone] <==> {Text}
     |-- [Fax] <==> {Text}
   Employees
     |-- [EmployeeID] <==> {AutoNumber}
     |-- [LastName] <==> {Text}
     |-- [FirstName] <==> {Text}
     |-- [Title] <==> {Text}
     |-- [TitleOfCourtesy] <==> {Text}
     |-- [BirthDate] <==> {Date/Time}
     |-- [HireDate] <==> {Date/Time}
     |-- [Address] <==> {Text}
     |-- [City] <==> {Text}
     |-- [Region] <==> {Text}
     |-- [PostalCode] <==> {Text}
     |-- [Country] <==> {Text}
     |-- [HomePhone] <==> {Text}
     |-- [Extension] <==> {Text}
     |-- [Photo] <==> {OLE Object}
     |-- [Notes] <==> {Memo}
     |-- [ReportsTo] <==> {Long Integer}
     |-- [Married] <==> {Yes/No}
     |-- [Age] <==> {Long Integer}
   Order Details
     |-- [OrderID] <==> {Long Integer}
     |-- [ProductID] <==> {Long Integer}
     |-- [UnitPrice] <==> {Currency}
     |-- [Quantity] <==> {Integer}
     |-- [Discount] <==> {Single}
     |-- [TestField] <==> {Text}
   Orders
     |-- [OrderID] <==> {AutoNumber}
     |-- [CustomerID] <==> {Text}
     |-- [EmployeeID] <==> {Long Integer}
     |-- [OrderDate] <==> {Date/Time}
     |-- [RequiredDate] <==> {Date/Time}
     |-- [ShippedDate] <==> {Date/Time}
     |-- [ShipVia] <==> {Long Integer}
     |-- [Freight] <==> {Currency}
     |-- [ShipName] <==> {Text}
     |-- [ShipAddress] <==> {Text}
     |-- [ShipCity] <==> {Text}
     |-- [ShipRegion] <==> {Text}
     |-- [ShipPostalCode] <==> {Text}
     |-- [ShipCountry] <==> {Text}
   Products
     |-- [ProductID] <==> {AutoNumber}
     |-- [ProductName] <==> {Text}
     |-- [SupplierID] <==> {Long Integer}
     |-- [CategoryID] <==> {Long Integer}
     |-- [QuantityPerUnit] <==> {Text}
     |-- [UnitPrice] <==> {Currency}
     |-- [UnitsInStock] <==> {Integer}
     |-- [UnitsOnOrder] <==> {Integer}
     |-- [ReorderLevel] <==> {Integer}
     |-- [Discontinued] <==> {Yes/No}
   Shippers
     |-- [ShipperID] <==> {AutoNumber}
     |-- [CompanyName] <==> {Text}
     |-- [Phone] <==> {Text}
   Suppliers
     |-- [SupplierID] <==> {AutoNumber}
     |-- [CompanyName] <==> {Text}
     |-- [ContactName] <==> {Text}
     |-- [ContactTitle] <==> {Text}
     |-- [Address] <==> {Text}
     |-- [City] <==> {Text}
     |-- [Region] <==> {Text}
     |-- [PostalCode] <==> {Text}
     |-- [Country] <==> {Text}
     |-- [Phone] <==> {Text}
     |-- [Fax] <==> {Text}
     |-- [HomePage] <==> {Hyperlink}
   tblTest
     |-- [ID] <==> {AutoNumber}
     |-- [Hyperlink] <==> {Hyperlink}
     |-- [Double] <==> {Double}
     |-- [Byte] <==> {Byte}
     |-- [Replication ID] <==> {GUID}
     |-- [Decimal] <==> {Decimal}

[NeoPa]

ADezii, at the risk of hijacking the thread (but it's on topic really) I tried your code and it worked fine.

I knocked up the following two procedures and was wondering if you could say why ShowFields() crashes while DisplayFields() produces results exactly as expected?

Public Sub ShowFields()
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
 
    Set tdf = CurrentDb.TableDefs("AssetPrices")
    Debug.Print tdf.Name
    For Each fld In tdf.Fields
        Debug.Print "  " & fld.Name
    Next fld
End Sub
 
Public Sub DisplayFields()
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
 
    For Each tdf In CurrentDb.TableDefs
        If tdf.Name = "AssetPrices" Then
            Debug.Print tdf.Name
            For Each fld In tdf.Fields
                Debug.Print "  " & fld.Name
            Next fld
        End If
    Next
End Sub

The only difference is in how tdf is set. Line #6 errors with "Object invalid or no longer set".

[ADezii]

In this specific context, you must explicitly set an Object Variable to point to a new Instance of the Current Database first, before you can reference its Lower Level Objects. The below syntax will work:

Public Sub ShowFields()
Dim MyDB As Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
 
Set MyDB = CurrentDb
 
Set tdf = MyDB.TableDefs("AssetPrices")
  Debug.Print tdf.Name
  For Each fld In tdf.Fields
    Debug.Print "  " & fld.Name
  Next fld
End Sub

[NeoPa]

Wow. I've often seen the explicit setting of CurrentDB to an object variable. I've never before seen an instance of why it is necessary.

It seems quite naff, but I wonder if there is a good (logical) reason for it somewhere I'm simply unaware of.

Anyway, I hope this illustrates fully to the OP (Sloney) what can be done to see all tables and fields.

[sloney]

This was very helpful! Thanks to all who posted!

Sloney

[NeoPa]

I'm pleased some of that helped.

I also had to learn a bit to be able to post it - so that's a double bonus :)