Sorting field names in table definitions 1

[jmstarbuck]

I'm looking for a utility that will sort the fields in a table definition alphabetically. Ideally, I would like to leave the indicies at the top and sort everything else, but that is not a requirement. I can move the indicies by hand if necessary.

I am also looking for a way to compare the table structures of two tables and highlight the differences in some way.

Does anyone have something like this? I'm not comfortable with the system tables, so I haven't tried to write something myself.

Any suggestions would be greatly appreciated.

J

 

[dhookom]

Can I ask why the order makes a difference? You can use the documenter to pull the tables structures. If you want greater flexibility check out Flexible Reporting of Documenter Results

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Same question as Duane. Why?

However, this can be done rather easily. Every field in a table definition has an ordinal position. Two or more fields can have the same ordinal position. If they have the same ordinal position they then sort alphabetically. So give each fld that is part of an idex an ordinal position of 0 and all the rest an ordinal position of one. It will then sort the indexed fields alphabetically followed by the non indexed alphabetically

Public Sub test(tblName)
  Dim db As DAO.Database
  Dim tdf As DAO.TableDef
  Dim flds As DAO.Fields
  Dim fld As DAO.Field
  Dim idxs As DAO.Indexes
  Dim idx As DAO.Index
  Dim tempIsIndex As Boolean
  
  Set db = CurrentDb
  Set tdf = db.TableDefs(tblName)
  Set flds = tdf.Fields
  Set idxs = tdf.Indexes
  For Each fld In flds
    tempIsIndex = False
    For Each idx In idxs
      If isIndex(fld.Name, idx) Then
        tempIsIndex = True
        Exit For
      End If
    Next idx
    
    If tempIsIndex Then
      fld.OrdinalPosition = 0
    Else
      fld.OrdinalPosition = 1
    End If
  Next fld
End Sub

Public Function isIndex(fldName As String, idx As DAO.Index) As Boolean
  Dim fld As DAO.Field
  For Each fld In idx.Fields
    If fld.Name = fldName Then
      isIndex = True
      Exit Function
    End If
  Next fld
End Function

 

[MajP]

For the comparison part, what exactly do you want to compare?

 

[jmstarbuck]

Hi all. Thanks for your replies. I want to sort the field names because I am tired of scrolling through table definitions to find what I am looking for. I have manually alphabetized some of my tables already and I love it.

I need to compare two table definitions because I am working on a migration. I have tables that are essentially the same that have had fields removed, added or renamed. I would like to be able do something like a 2 file compare on the table definitions to highlight the differences.

J

 

[MajP]

2 tables with differences: Shippers and Shippers2

Public Function getSameFields(tblOne As String, tblTwo As String) As String
  Dim db As DAO.Database
  Dim strDocument As String
  Dim tdf1 As DAO.TableDef
  Dim tdf2 As DAO.TableDef
  Dim fld1 As DAO.Field
  Dim fld2 As DAO.Field
  
  Set db = CurrentDb
  Set tdf1 = db.TableDefs(tblOne)
  Set tdf2 = db.TableDefs(tblTwo)
  
  strDocument = "TABLE ONE: " & tblOne & "      TABLE TWO: " & tblTwo & vbCrLf & vbCrLf
  strDocument = strDocument & "Fields the Same:" & vbCrLf
  For Each fld1 In tdf1.Fields
    For Each fld2 In tdf2.Fields
      If fld2.Name = fld1.Name Then
        strDocument = strDocument & "  " & fld1.Name & "   " & fld2.Name & vbCrLf
      End If
    Next fld2
  Next fld1
  getSameFields = strDocument
End Function
Public Function getInOneNotInTwo(tblOne As String, tblTwo As String) As String
  Dim db As DAO.Database
  Dim strDocument As String
  Dim tdf1 As DAO.TableDef
  Dim tdf2 As DAO.TableDef
  Dim fld1 As DAO.Field
  Dim fld2 As DAO.Field
  Dim blnFound As Boolean
  Set db = CurrentDb
  Set tdf1 = db.TableDefs(tblOne)
  Set tdf2 = db.TableDefs(tblTwo)
  
  strDocument = "TABLE ONE: " & tblOne & "      TABLE TWO: " & tblTwo & vbCrLf & vbCrLf
  strDocument = strDocument & "Fields in " & tblOne & " not in " & tblTwo & vbCrLf
  For Each fld1 In tdf1.Fields
    blnFound = False
    For Each fld2 In tdf2.Fields
      If fld2.Name = fld1.Name Then
         blnFound = True
      End If
    Next fld2
    If Not blnFound Then
       strDocument = strDocument & "  " & fld1.Name & vbCrLf
    End If
  Next fld1
  getInOneNotInTwo = strDocument
End Function
Public Function getInTwoNotInOne(tblOne As String, tblTwo As String) As String
  Dim db As DAO.Database
  Dim strDocument As String
  Dim tdf1 As DAO.TableDef
  Dim tdf2 As DAO.TableDef
  Dim fld1 As DAO.Field
  Dim fld2 As DAO.Field
  Dim blnFound As Boolean
  Set db = CurrentDb
  Set tdf1 = db.TableDefs(tblOne)
  Set tdf2 = db.TableDefs(tblTwo)
  
  strDocument = "TABLE ONE: " & tblOne & "      TABLE TWO: " & tblTwo & vbCrLf & vbCrLf
  strDocument = strDocument & "Fields in " & tblTwo & " not in " & tblOne & vbCrLf
  For Each fld2 In tdf2.Fields
    blnFound = False
    For Each fld1 In tdf1.Fields
      If fld2.Name = fld1.Name Then
        blnFound = True
      End If
    Next fld1
    If Not blnFound Then
       strDocument = strDocument & "  " & fld2.Name & vbCrLf
    End If
  Next fld2
  getInTwoNotInOne = strDocument
End Function


Public Sub test()
  Debug.Print getSameFields("Shippers", "Shippers2")
  Debug.Print getInOneNotInTwo("shippers", "Shippers2")
  Debug.Print getInTwoNotInOne("Shippers", "Shippers2")
End Sub

output

TABLE ONE: Shippers      TABLE TWO: Shippers2
Fields the Same:
  ShipperID   ShipperID
  Phone   Phone

Fields in shippers not in Shippers2
  CompanyName
  fieldInOneNotIn2

Fields in Shippers2 not in Shippers
  CompanyName2
  fieldIn2NotInOne

 

[jmstarbuck]

Thank you so much! This is awesome and will save me hours of work. THANK YOU!!

 

[MajP]

Take a look at faq700-6905. There are some additional functions you may want to use.
For example there are functions to determine the datatype, if the field is a primary key, if it is part of an index, and if the field is a foreign key.

So you may also want to verify if
fields with the same name still have the same datatype
fields with the same name are still both primary keys
fields with the same name are still both foreign keys
fields with the same name are still both indexed.

You should be able to follow what I did to add this functionality.