Sorting field names in code

[Moniker]

Before I embark on writing something to do this, does anyone know of a way to sort the field names (not the field contents) so that in Table Design view, they are sorted alphabetically?

For example, these fields:

LName
FName
Addr1
Addr2
City
State
Zip
Phone
Email

Would become this in table design view:

Addr1
Addr2
City
Email
FName
LName
Phone
State
Zip

This would be very helpful for comparing tables, especially when the tables have 20+ fields.

If there's not already existing code or some internal way to do this that I just don't know of, then I'm going to write the routine myself. However, I don't want to reinvent the wheel if it's not necessary.

 

[gemma-the-husky]

needs a bit of work, andyou probably will need an array to sort the fields, as vb doesnt have pointer types,. you could use a collection although i find these unwieldy

you can get at the field names by

dim fld as field
dim tdf as tabledef
(you might need to define a database variable to hold the currentdb

set tdf = currentdb.tabledefs(yourtablename)
for each fld in tdf.fields
'and in this part
fld.name 'will reference the field name - so you can store it in your array etc
next fld

 

[Moniker]

I know how to program it; instead, I'm trying to make sure that it doesn't already exist so that I don't rewrite what someone already has. A Google search comes up empty for the most part (someone had written a custom add-in of all things), and it's not horribly difficult to write, just a little tedious.

FYI, I'm just reading the field names into a table, sorting that table, and then using it as the basis for creating a new table. The pain part is copying all the properties of each field (Default Value, Validation Rule, etc.), but I can loop through the enumerated properties for each one easy enough. The trick is going to be getting existing indexes and properly structuring the table to be identical to the original table, but sorted.

 

[Moniker]

For anyone curious, I'm 95% of the way there. This will handle nearly all situations and sort the field names for you, and it even gives you a few options. It's not optimized and there are a few things it won't do, but it handles field names, field types, field sizes, indexes, and primary keys.

I don't want to post the whole thing, but if you're curious as to how this works, you'll need a few things. In a new DB, make a table called t_Field_List with these fields:

FieldName (Text)
FieldType (Text)
FieldSize (Number - Long Integer)
IsPrimaryField (Yes/No)

Make a form with these things on it:

A listbox named lstTableNames. Set its RowSource property to this:

SELECT Name FROM MSysObjects WHERE Type In (1,6) And Left(Name,4)<>"MSys" ORDER BY Name;

A frame named fraSortOrder with two option buttons, optSortAscending (Value = 1) and optDescending (Value = 2).

A Checkbox named chkPrimaryKeyTop. (Checking this box will sort the primary keys separately and place them at the top of the table in design view.)

A command button named cmdProcess, with this as its click event:

Sub cmdProcess_Click()

    Dim ctr As Byte
    Dim ctr2 As Byte
    Dim rsFields As ADODB.Recordset
    Dim strOrigTableName As String
    Dim strNewTableName As String
    Dim idxNewIndexes As Index
    Dim strSortOrder As String
    Dim strWhereStatement As String
    Dim bytFieldCount As Byte
    Dim bytIndexCount As Byte
    Dim intPrimaryIndex As Integer
    Dim colPrimaryFields As Collection
    Dim bytPrimaryCount As Byte
    Dim bolPrimaryOn As Boolean
    
    CurrentDb.Execute "DELETE * FROM t_Field_List"
            
    strOrigTableName = lstTableNames
    intPrimaryIndex = -1
    Set colPrimaryFields = New Collection
    
    bytIndexCount = CurrentDb.TableDefs(strOrigTableName).Indexes.Count
    If bytIndexCount > 0 Then
        For ctr = 0 To bytIndexCount - 1
            If CurrentDb.TableDefs(strOrigTableName).Indexes(ctr).Primary Then
                intPrimaryIndex = ctr
                bytPrimaryCount = CurrentDb.TableDefs(strOrigTableName).Indexes(ctr).Fields.Count
                For ctr2 = 0 To CurrentDb.TableDefs(strOrigTableName).Indexes(ctr).Fields.Count - 1
                    colPrimaryFields.Add CurrentDb.TableDefs(strOrigTableName).Indexes(ctr).Fields(ctr2).Name
                Next
            End If
        Next
    End If
    
    bytFieldCount = CurrentDb.TableDefs(strOrigTableName).Fields.Count
    If bytFieldCount > 0 Then
        For ctr = 0 To bytFieldCount - 1
            bolPrimaryOn = False
            For ctr2 = 1 To bytPrimaryCount
                If colPrimaryFields(ctr2) = CurrentDb.TableDefs(strOrigTableName).Fields(ctr).Name Then
                    bolPrimaryOn = True
                End If
            Next
        CurrentDb.Execute "INSERT INTO t_Field_List (FieldName, FieldType, FieldSize, IsPrimaryField) VALUES ('" & _
        CurrentDb.TableDefs(strOrigTableName).Fields(ctr).Name & "', '" & _
        CurrentDb.TableDefs(strOrigTableName).Fields(ctr).Type & "', " & _
        CurrentDb.TableDefs(strOrigTableName).Fields(ctr).Size & ", " & _
        bolPrimaryOn & ");"
        Next
    End If
    
    strNewTableName = lstTableNames & "_NEW"
    strSortOrder = Switch(fraSortOrder = 1, "ASC", True, "DESC")
    strWhereStatement = Switch(chkPrimaryKeyTop, "WHERE IsPrimaryField = True", True, "")
    
    CurrentDb.Execute "CREATE TABLE " & strNewTableName & ";"
    Set rsFields = New ADODB.Recordset
    
    If strWhereStatement <> "" Then
        rsFields.Open "SELECT * FROM t_Field_List " & strWhereStatement & " ORDER BY FieldName " & strSortOrder, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
        If Not rsFields.EOF Then
            With rsFields
                .MoveFirst
                While Not .EOF
                    CurrentDb.TableDefs(strNewTableName).Fields.Append CurrentDb.TableDefs(strNewTableName).CreateField(.Fields("FieldName"), .Fields("FieldType"), .Fields("FieldSize"))
                    .MoveNext
                Wend
                .Close
            End With
        End If
        strWhereStatement = "WHERE IsPrimaryField = False"
    End If
    
    rsFields.Open "SELECT * FROM t_Field_List " & strWhereStatement & " ORDER BY FieldName " & strSortOrder, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    With rsFields
        .MoveFirst
        While Not .EOF
            CurrentDb.TableDefs(strNewTableName).Fields.Append CurrentDb.TableDefs(strNewTableName).CreateField(.Fields("FieldName"), .Fields("FieldType"), .Fields("FieldSize"))
            .MoveNext
        Wend
        .Close
    End With
    
    bytIndexCount = CurrentDb.TableDefs(strOrigTableName).Indexes.Count
    If bytIndexCount > 0 Then
        For ctr = 0 To bytIndexCount - 1
            Set idxNewIndexes = CurrentDb.TableDefs(strNewTableName).CreateIndex
            idxNewIndexes.Name = CurrentDb.TableDefs(strOrigTableName).Indexes(ctr).Name
            bytFieldCount = CurrentDb.TableDefs(strOrigTableName).Indexes(ctr).Fields.Count
            If bytFieldCount > 0 Then
                For ctr2 = 0 To bytFieldCount - 1
                    idxNewIndexes.Fields.Append idxNewIndexes.CreateField(CurrentDb.TableDefs(strOrigTableName).Indexes(ctr).Fields(ctr2).Name)
                    If CurrentDb.TableDefs(strOrigTableName).Indexes(ctr).Primary Then
                        idxNewIndexes.Primary = True
                    End If
                Next
            End If
            CurrentDb.TableDefs(strNewTableName).Indexes.Append idxNewIndexes
        Next
    End If
    
    Set rsFields = Nothing
    Set colPrimaryFields = Nothing

End Sub

To use the form, select a table from the table listbox you made, and click the cmdProcess button. A table with "_NEW" appended to the end will be created, and your sort will be done.

I'm passing this along because I found a few places that were actually selling software that does this (usually as a package that had a few other little apps like this). Once I have it bulletproof, I'll post it as a stand-alone DB that will work with any DB.

Enjoy!