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.

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

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.

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.Coun t
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.Coun t
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(strO rigTableName).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.Appen d 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! ;)