<SNIP>An option is to build base queries with the fields in alpha order and
then use
that base query for all your queries.</SNIP>
Here's one way to crank out the queries automatically with all the fields
included in alpha order...
Option Compare Database
Option Explicit
Function ShowAllTables(Optional bShowFieldsToo As Boolean)
'Purpose: List the tables (and optionally their fields) using ADOX.
Dim cat As New ADOX.catalog 'Root object of ADOX.
Dim tbl As ADOX.Table 'Each Table in Tables.
Dim col As ADOX.Column 'Each Column in the Table.
'Point the catalog to the current project's connection.
Set cat.ActiveConnection = CurrentProject.Connection
'Loop through the tables.
For Each tbl In cat.Tables
Debug.Print tbl.name, tbl.Type
If bShowFieldsToo Then
'Loop through the columns of the table.
For Each col In tbl.Columns
Debug.Print , col.name, col.Type
Next
Debug.Print "--------------------------------"
'Stop
End If
Next
'Clean up
Set col = Nothing
Set tbl = Nothing
Set cat = Nothing
End Function
Private Function ShowColumsOfTable(ByVal strTable As String) As String
'tweak of Allen Browne's code for ShowAllTables...
'found here:
http://www.allenbrowne.com/func-ADOX.html
'Purpose: return an alphabetized list of columns
Dim cat As New ADOX.catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim strFieldList As String ' temp variable to accumulate field list into
'Point the catalog to the current project's connection
Set cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(strTable)
'Columns are already sorted... append them to a string
For Each col In tbl.Columns
strFieldList = strFieldList & "[" & col.name & "], "
Next col
' trim off trailing comma-space
ShowColumsOfTable = Left$(strFieldList, Len(strFieldList) - 2)
End Function
Public Function CreateQueryWithAlphaColumns(ByVal strTable As String) As
String
Dim strSQL As String
Dim strFieldList As String
strFieldList = ShowColumsOfTable(strTable)
strSQL = "SELECT " & strFieldList & " FROM [" & strTable & "];"
CreateQueryWithAlphaColumns = strSQL
'you could turn this into a sub and create a querydef using DAO here...
End Function
Function CreateQueryDAO(ByVal strTable As String)
'Purpose: How to create a query
'Note: Requires a table named MyTable.
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strQueryName As String
'assuming no spaces in table name
strQueryName = "qryAlpha_" & strTable
Set db = CurrentDb()
'The next line creates and automatically appends the QueryDef.
Set qdf = db.CreateQueryDef(strQueryName)
'Set the SQL property to a string representing a SQL statement.
qdf.SQL = CreateQueryWithAlphaColumns(strTable)
'Do not append: QueryDef is automatically appended!
Set qdf = Nothing
Set db = Nothing
Debug.Print strQueryName & " created."
End Function
