By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,034 Members | 1,777 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,034 IT Pros & Developers. It's quick & easy.

Select Tables that have the same attribute (column)

P: 8
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
Aug 29 '08 #1
Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,419
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.
Aug 29 '08 #2

NeoPa
Expert Mod 15k+
P: 31,419
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).
Expand|Select|Wrap|Line Numbers
  1. 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.
Expand|Select|Wrap|Line Numbers
  1. Public Sub ShowFields(strTable As String)
  2.     Dim intIx As Integer
  3.  
  4.     For intIx = 0 To CurrentDb.TableDefs(strTable).Fields.Count - 1
  5.         Debug.Print CurrentDb.TableDefs(strTable).Fields(intIx).Name
  6.     Next intIx
  7. End Sub
The following, for instance, should be equivalent and work exactly the same. It didn't for me :(
Expand|Select|Wrap|Line Numbers
  1. Public Sub ShowFields(strTable As String)
  2.     Dim intIx As Integer
  3.  
  4.     With CurrentDb.TableDefs(strTable)
  5.         For intIx = 0 To .Fields.Count - 1
  6.             Debug.Print .Fields(intIx).Name
  7.         Next intIx
  8.     End With
  9. End Sub
Aug 29 '08 #3

ADezii
Expert 5K+
P: 8,623
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:
    Expand|Select|Wrap|Line Numbers
    1. Dim tdf As DAO.TableDef
    2. Dim fld As DAO.Field
    3.  
    4. For Each tdf In CurrentDb.TableDefs
    5.   If Left$(tdf.Name, 4) Like "*Sys" Then
    6.     'Ignore System Tables
    7.   Else
    8.     Debug.Print tdf.Name
    9.       For Each fld In tdf.Fields
    10.         Debug.Print "  |-- [" & fld.Name & "] <==> {" & _
    11.                        fReturnFieldType(fld) & "}"
    12.       Next
    13.   End If
    14. Next
  2. Function Procedure:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fReturnFieldType(fldRet As DAO.Field)
    2. Dim strReturn As String
    3.  
    4. Select Case fldRet.Type
    5.   Case dbBoolean: strReturn = "Yes/No"
    6.   Case dbByte: strReturn = "Byte"
    7.   Case dbInteger: strReturn = "Integer"
    8.   Case dbLong
    9.     If (fldRet.Attributes And dbAutoIncrField) = 0& Then
    10.       strReturn = "Long Integer"
    11.     Else
    12.       strReturn = "AutoNumber"
    13.     End If
    14.   Case dbCurrency: strReturn = "Currency"
    15.   Case dbSingle: strReturn = "Single"
    16.   Case dbDouble: strReturn = "Double"
    17.   Case dbDate: strReturn = "Date/Time"
    18.   Case dbBinary: strReturn = "Binary"
    19.   Case dbText
    20.     If (fldRet.Attributes And dbFixedField) = 0& Then
    21.       strReturn = "Text"
    22.     Else
    23.       strReturn = "Text [Fixed Width]"
    24.     End If
    25.   Case dbMemo
    26.     If (fldRet.Attributes And dbHyperlinkField) = 0& Then
    27.       strReturn = "Memo"
    28.     Else
    29.       strReturn = "Hyperlink"
    30.     End If
    31.   Case dbLongBinary: strReturn = "OLE Object"
    32.   Case dbGUID: strReturn = "GUID"
    33.   Case dbDecimal: strReturn = "Decimal"
    34.   Case Else: strReturn = "Unknown"
    35. End Select
  3. Sample OUTPUT:
    Expand|Select|Wrap|Line Numbers
    1. Categories
    2.   |-- [CategoryID] <==> {AutoNumber}
    3.   |-- [CategoryName] <==> {Text}
    4.   |-- [Description] <==> {Memo}
    5.   |-- [Picture] <==> {OLE Object}
    6. Customers
    7.   |-- [CustomerID] <==> {Text}
    8.   |-- [CompanyName] <==> {Text}
    9.   |-- [ContactName] <==> {Text}
    10.   |-- [ContactTitle] <==> {Text}
    11.   |-- [Address] <==> {Text}
    12.   |-- [City] <==> {Text}
    13.   |-- [Region] <==> {Text}
    14.   |-- [PostalCode] <==> {Text}
    15.   |-- [Country] <==> {Text}
    16.   |-- [Phone] <==> {Text}
    17.   |-- [Fax] <==> {Text}
    18. Employees
    19.   |-- [EmployeeID] <==> {AutoNumber}
    20.   |-- [LastName] <==> {Text}
    21.   |-- [FirstName] <==> {Text}
    22.   |-- [Title] <==> {Text}
    23.   |-- [TitleOfCourtesy] <==> {Text}
    24.   |-- [BirthDate] <==> {Date/Time}
    25.   |-- [HireDate] <==> {Date/Time}
    26.   |-- [Address] <==> {Text}
    27.   |-- [City] <==> {Text}
    28.   |-- [Region] <==> {Text}
    29.   |-- [PostalCode] <==> {Text}
    30.   |-- [Country] <==> {Text}
    31.   |-- [HomePhone] <==> {Text}
    32.   |-- [Extension] <==> {Text}
    33.   |-- [Photo] <==> {OLE Object}
    34.   |-- [Notes] <==> {Memo}
    35.   |-- [ReportsTo] <==> {Long Integer}
    36.   |-- [Married] <==> {Yes/No}
    37.   |-- [Age] <==> {Long Integer}
    38. Order Details
    39.   |-- [OrderID] <==> {Long Integer}
    40.   |-- [ProductID] <==> {Long Integer}
    41.   |-- [UnitPrice] <==> {Currency}
    42.   |-- [Quantity] <==> {Integer}
    43.   |-- [Discount] <==> {Single}
    44.   |-- [TestField] <==> {Text}
    45. Orders
    46.   |-- [OrderID] <==> {AutoNumber}
    47.   |-- [CustomerID] <==> {Text}
    48.   |-- [EmployeeID] <==> {Long Integer}
    49.   |-- [OrderDate] <==> {Date/Time}
    50.   |-- [RequiredDate] <==> {Date/Time}
    51.   |-- [ShippedDate] <==> {Date/Time}
    52.   |-- [ShipVia] <==> {Long Integer}
    53.   |-- [Freight] <==> {Currency}
    54.   |-- [ShipName] <==> {Text}
    55.   |-- [ShipAddress] <==> {Text}
    56.   |-- [ShipCity] <==> {Text}
    57.   |-- [ShipRegion] <==> {Text}
    58.   |-- [ShipPostalCode] <==> {Text}
    59.   |-- [ShipCountry] <==> {Text}
    60. Products
    61.   |-- [ProductID] <==> {AutoNumber}
    62.   |-- [ProductName] <==> {Text}
    63.   |-- [SupplierID] <==> {Long Integer}
    64.   |-- [CategoryID] <==> {Long Integer}
    65.   |-- [QuantityPerUnit] <==> {Text}
    66.   |-- [UnitPrice] <==> {Currency}
    67.   |-- [UnitsInStock] <==> {Integer}
    68.   |-- [UnitsOnOrder] <==> {Integer}
    69.   |-- [ReorderLevel] <==> {Integer}
    70.   |-- [Discontinued] <==> {Yes/No}
    71. Shippers
    72.   |-- [ShipperID] <==> {AutoNumber}
    73.   |-- [CompanyName] <==> {Text}
    74.   |-- [Phone] <==> {Text}
    75. Suppliers
    76.   |-- [SupplierID] <==> {AutoNumber}
    77.   |-- [CompanyName] <==> {Text}
    78.   |-- [ContactName] <==> {Text}
    79.   |-- [ContactTitle] <==> {Text}
    80.   |-- [Address] <==> {Text}
    81.   |-- [City] <==> {Text}
    82.   |-- [Region] <==> {Text}
    83.   |-- [PostalCode] <==> {Text}
    84.   |-- [Country] <==> {Text}
    85.   |-- [Phone] <==> {Text}
    86.   |-- [Fax] <==> {Text}
    87.   |-- [HomePage] <==> {Hyperlink}
    88. tblTest
    89.   |-- [ID] <==> {AutoNumber}
    90.   |-- [Hyperlink] <==> {Hyperlink}
    91.   |-- [Double] <==> {Double}
    92.   |-- [Byte] <==> {Byte}
    93.   |-- [Replication ID] <==> {GUID}
    94.   |-- [Decimal] <==> {Decimal}
Aug 30 '08 #4

NeoPa
Expert Mod 15k+
P: 31,419
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?
Expand|Select|Wrap|Line Numbers
  1. Public Sub ShowFields()
  2.     Dim tdf As DAO.TableDef
  3.     Dim fld As DAO.Field
  4.  
  5.     Set tdf = CurrentDb.TableDefs("AssetPrices")
  6.     Debug.Print tdf.Name
  7.     For Each fld In tdf.Fields
  8.         Debug.Print "  " & fld.Name
  9.     Next fld
  10. End Sub
  11.  
  12. Public Sub DisplayFields()
  13.     Dim tdf As DAO.TableDef
  14.     Dim fld As DAO.Field
  15.  
  16.     For Each tdf In CurrentDb.TableDefs
  17.         If tdf.Name = "AssetPrices" Then
  18.             Debug.Print tdf.Name
  19.             For Each fld In tdf.Fields
  20.                 Debug.Print "  " & fld.Name
  21.             Next fld
  22.         End If
  23.     Next
  24. End Sub
The only difference is in how tdf is set. Line #6 errors with "Object invalid or no longer set".
Aug 30 '08 #5

ADezii
Expert 5K+
P: 8,623
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:
Expand|Select|Wrap|Line Numbers
  1. Public Sub ShowFields()
  2. Dim MyDB As Database
  3. Dim tdf As DAO.TableDef
  4. Dim fld As DAO.Field
  5.  
  6. Set MyDB = CurrentDb
  7.  
  8. Set tdf = MyDB.TableDefs("AssetPrices")
  9.   Debug.Print tdf.Name
  10.   For Each fld In tdf.Fields
  11.     Debug.Print "  " & fld.Name
  12.   Next fld
  13. End Sub
Aug 30 '08 #6

NeoPa
Expert Mod 15k+
P: 31,419
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.
Aug 30 '08 #7

P: 8
This was very helpful! Thanks to all who posted!

Sloney
Sep 2 '08 #8

NeoPa
Expert Mod 15k+
P: 31,419
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 :)
Sep 2 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.