Add fields to a DAO recordset with VBA

[smbrush]

I have a DAO recordset with one field. I want to add other fields with VBA code. There is a method called recordset.fields.append but this requires a field object of some sort. Can anyone provide me with the code to add new fields to a DAO recordset with VBA code?

[orange]

see this by Allen Browne

[smbrush]

Your link doesn't help at all. There is nothing in all those code examples that answers my question. Is there a way to add fields to a DAO recordset with VBA or not? This is not a tabledef or querydef.

[orange]

If recordset is based on a table==
You could use code as below:

Sub addFieldtoTable()
    'This adds a COLUMN named anotherfieldBB of datatype Long
    ' to an existing table called Patient9
 '** NOTE: With Access SQL you can only add 1 field at a time/
 '               To add multiple fields, run the query multiple times

    Dim x As String: x = "ALTER TABLE Patient9 " _
    & "ADD column   anotherfieldBB  long);"
    Debug.Print x
    CurrentProject.Connection.Execute x, dbFailOnError
End Sub

What is the source of your recordset?

See if these help
https://social.msdn.microsoft.com/Forums/office/en-US/0b4b58d4-dfe5-4a91-b99d-abe885c180b8/adding-fields-to-a-recordset?forum=accessdev

https://docs.microsoft.com/en-us/off...-dao-recordset

[June7]

Exactly what do you mean by 'add new fields to a DAO recordset with VBA code'? Yes, a field can be constructed with a calculation same as in a query object.

If you want to modify a table object, then use SQL ALTER TABLE or TableDefs code.

What exactly are you trying to accomplish?

[smbrush]

This also doesn't help me. Please read my original post. I need to create a DAO recordset programmatically using values from a field in a table as field names. These values can change over time so I need to iterate over all the records in that table using another recordset.

I start by creating my recordset from a SQL query such as: "select fldNames from tbl1 order by fldNames" and use "set rs.recordset = db.openrecordset(sql query)". I would then like to iterate through all records from an existing table and set them up as field names in my recordset. Ideally, I would like to use the following code or some version of it:

set rsFldName = db.OpenRecordset("select fldNames from tbl1 order by fldNames")
set rsMyRecordSet = db.OpenRecordset("select indexnum from tbl2")

rsFldName.MoveFirst
Do while not rsFldName.EOF
rsMyRecordSet.Fields.Append rsFldName!fldNames, char(50)
fsFldName.MoveNext
loop

This code won't work because .Fields.Append needs an object variable and not a string of the desired field name. I can't figure out how to set this field object variable.

Do you or anyone else know how best to programmatically add fields to a DAO recordset object?

[orange]

I would then like to iterate through all records from an existing table and set them up as field names in my recordset.

Suppose your existing table has this design:

table_name
field_name
data_type
length

patient9
SSN
Text
9

patient9
PID
Text
8

patient9
Address
Text
60

patient9
BirthDate
Date
8

patient9
Gender
Text
1

patient9
xName
Text
20

patient9
BloodType
Text
2

patient9
anotherfield
Text
25

patient9
anotherfieldA
Text
6

patient9
anotherfieldBB
Long
4

You can get all the field names in a recordset with some vba

Sub test_smbrush()
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Set rs = CurrentDb.OpenRecordset("Patient9")
For Each fld In rs.Fields
 Debug.Print fld.name
Next
End Sub

Output:

SSN
PID
Address
BirthDate
Gender
xName
BloodType
anotherfield
anotherfieldA
anotherfieldBB


If this isn't what you need/want, then please be specific. Your understanding of DAO recordset in your context is not clear.

[June7]

Well, your original post was very uninformative. Post 6 finally gave substantial enough information to begin understanding situation.

You want the value in each record to be used as field name. Just build a string.

Set rsFldName = db.OpenRecordset("select fldNames from tbl1 order by fldNames")
While not rsFldName.EOF
    strSQL = strSQL & "[" & rsFldName![fldNames] & "],"
    rsFldName.MoveNext
Wend
Set rsMyRecordSet = db.OpenRecordset("SELECT indexnum," & Left(strSQL, Len(strSQL)-1) & " FROM tbl2;")

[davegri]

Still not clear to me.
In the table containing the names that you want to use for field names in your output file:
If you have a field named STATUS and the contents of that field is "FINAL", do you want the output field name to be STATUS or FINAL?
Then, in the next record the field STATUS contains the value "OPEN". What do you want for the output file field name?

If you are using the contents, what happens when the field content is a date or a number?

[orange]

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.

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