After seeing the comments by June and Davegri, I am offering the following.
I have a table called data_dictionary which I rebuild and populate with a DocumentTables routine.
The table has this design
Here is a routine to query this table, and using fields in the table to create a recordset RS_SMBRUSH.
This recordset is created based on a table_name within data_dictionary and the field_names that are associated with the table_name.
For this demo I used/selected a table named Inventory
I have used some debug.prints to show some of the interim results.
The sqlFinal shows the SQL for the recordset.
The names of the fields in the recordset are listed at the bottom.
Code:
Sub test_smbrush_1()
10 On Error GoTo test_smbrush_1_Error
Dim sTableName As String
Dim SQLFlds As String
Dim SQLFinal As String
Dim i As Integer
Dim rs As DAO.Recordset
Dim RS_SMBRUSH As DAO.Recordset
Dim fld As DAO.Field
20 Set rs = CurrentDb.OpenRecordset("Data_Dictionary")
'show the table structure of Data_Dictionary
30 Debug.Print "Field names in Recordset (rs)"
40 For Each fld In rs.Fields
50 Debug.Print fld.name
60 Next
70 Debug.Print
'show the Table names in the data_dictionary
80 Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT table_name from Data_Dictionary")
90 Do While Not rs.EOF
'Debug.Print rs!table_name 'Commented because of volume
100 rs.MoveNext
110 Loop
'Pick a table from the list (I picked 'Inventory' for this demo
'Show the fields available in the chosen Table
120 Set RS_SMBRUSH = CurrentDb.OpenRecordset("Select field_name from data_dictionary where table_name ='Inventory'")
130 Do While Not RS_SMBRUSH.EOF
140 SQLFlds = SQLFlds & RS_SMBRUSH!field_name & ","
150 RS_SMBRUSH.MoveNext
160 Loop
'Show the raw fields list before trimming and forming the Select
170 Debug.Print SQLFlds
180 SQLFinal = "SELECT " & Mid(SQLFlds, 1, Len(SQLFlds) - 1) & " From Inventory"
190 Debug.Print
200 Debug.Print SQLFinal
210 Set RS_SMBRUSH = CurrentDb.OpenRecordset(SQLFinal)
220 Debug.Print
230 For Each fld In RS_SMBRUSH.Fields
240 Debug.Print vbTab & fld.name
250 Next
260 On Error GoTo 0
270 Exit Sub
test_smbrush_1_Error:
280 MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure test_smbrush_1, line " & Erl & "."
End Sub
This is the debug.print from the routine.
Field names in Recordset (rs)
EntryID
table_name
table_description
field_name
field_description
ordinal_position
data_type
length
default
Cabinet,issdate,MasterCopies,Media,Revision,RowId, units,WorkCopies,
SELECT Cabinet,issdate,MasterCopies,Media,Revision,RowId, units,WorkCopies From Inventory
Cabinet
issdate
MasterCopies
Media
Revision
RowId
units
WorkCopies