Adding Records in a Table using ACCESS VBA

Question

My Code is:

Public Sub ImportCommonFields()

    Set rs1 = CurrentDb.OpenRecordset("Imported Table" & " " & TableCtr)
    Set cf = CurrentDb.OpenRecordset("CommonFields")
    
    cf.AddNew
    cf("FieldNames") = rs1.Fields

    cf.Update
    Set fld = Nothing

End Sub

I am currently getting the column names (Fields) from the table in rs1 and would like to import them to an existing table "CommonFields" under the column "FieldNames".

It's not really clear what you want to do. Do you want to add a record in cf for each field in rs1 ? If that's the case then you need to loop over the rs1 fields and call cf.AddNew for each field. — Tim Williams
– Tim Williams, Commented Sep 2, 2020 at 21:46
You want to save field names of one table into a single column of another table? Why - what is the ultimate goal? — June7
– June7, Commented Sep 3, 2020 at 3:51
I think OP just want to add records from a table of specific column to another table. — Harun24hr
– Harun24hr, Commented Sep 3, 2020 at 6:11
Hi guys correct. The table Imported Table will always change and i would like to keep all the column names of all the tables that has been imported — Jay Paredes
– Jay Paredes, Commented Sep 3, 2020 at 7:15

Gustav · Accepted Answer · 2020-09-03 09:00:16Z

1

Loop the Fields collection:

Public Sub ImportCommonFields()

    Dim fld     As DAO.Field

    Set rs1 = CurrentDb.OpenRecordset("Imported Table" & " " & TableCtr)
    Set cf = CurrentDb.OpenRecordset("CommonFields")
    
    For Each fld In rs1.Fields
        cf.AddNew
        cf("FieldNames").Value = fld.Name
        cf.Update
    Next

    cf.Close
    rs1.Close

    Set fld = Nothing

End Sub

answered Sep 3, 2020 at 9:00

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding Records in a Table using ACCESS VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related