How can I loop through Access Field Names by index?

Question

I am trying to build an Access db of inspection data for mass manufactured plastic parts from a bunch of Excel sheets, which come from different sources.

I am doing this with two Access files: one as the front end/UI (Forms and Macros), and one as the back end (Tables containing the desired data).

My process:

Use front end Access Form to open the msoFileDialogFilePicker and select an Excel sheet of data to get read into my back end Access Table. The Table where the data is stored in the back end file depends on radio buttons selections in the front end file Form.
Macro reads data from Excel sheet, transposes data, and stores it in a new dumpSheet at the end of the original data file. Each row in this dumpSheet is effectively its own record in the corresponding back end Access table. Data gets read into back end Access Tables using DAO.Database and DAO.Recordset.
Repeat with new inspection data as it comes in every two weeks.

The inspection data comes from different sources, but it can be for the same plastic part. This combination of company + part dictates which fields are of interest to be read into the recordset. For example, all of these combinations would have different fields (each combination has its own table), but there is new inspection data for each combination every two weeks (hence the need for a db):

Company X + Part A
Company X + Part B
Company Y + Part A
Company Y + Part B

Here is a general look at how I'm setting this up in the Access front end Form Macro (where the fields listed in the Do While Loop are for one specific Company + Part combination:

Set excelTemp = New Excel.Application
excelTemp.Visible = True
excelTemp.DisplayAlerts = True
Set trExcelWB = excelTemp.Workbooks.Open("myExcelData.xlsx")  'Picked from msoDialogFilePicker

Dim ws As Worksheet
Set ws = trExcelWB.Sheets(Sheets.Count)

Dim dbDump As DAO.Database
Set dbDump = DBEngine.Workspaces(0).OpenDatabase("backend-database.accdb")

Dim dumpTable as String
dumpTable = "myTableForDataInBackEndDB"

Dim tdf As DAO.TableDef
Set tdf = dbDump.TableDefs(dumpTable)

Dim rst As DAO.Recordset
Set rst = dbDump.OpenRecordset(Name:=dumpTable)

With rst
    .AddNew
    Dim n as Integer
    n = 2
    
    Do While Len(ws.Cells(n,5).Value) > 0  '5th column of dumpSheet stores unique part IDs and governs end of my dataset on the dumpSheet
        .AddNew
                
        ![Date of Inspection] = ws.Cells(n, 2).Value
        ![Date of Production] = ws.Cells(n, 3).Value
        ![LOT] = ws.Cells(n, 4).Value
        ![Serial] = ws.Cells(n, 5).Value
        ![Part] = ws.Cells(n, 6).Value
               
        ![Depth] = ws.Cells(n, 7).Value
        ![Wall thickness] = ws.Cells(n, 8).Value

        .Update

        n = n + 1

    Loop

Which works in the case where I have:

Date of Inspection
Date of Production
LOT
Serial
Part
Depth
Wall Thickness

As my field names. These are my headers in only one case of many.

I would like something like this in my Do While Loop:

Do While Len(ws.Cells(n,5).Value) > 0

    Dim col as Integer
    For col = 2 To ws.UsedRange.Columns.Count
        .AddNew
        ![dbDump.TableDefs(dumpTable).Fields(col - 1).Name] = ws.Cells(n, col).Value
    Next col

    .Update

    n = n + 1

Loop

I get an Error

"Item not found in Collection".

I'm confused because dbDump.TableDefs(dumpTable).Fields(col - 1).Name on its own gives me the name of the field I want as a string, but I cannot figure out how to call it in the dbDump from this code.

I've looked and can't figure out how to loop through field names via index and not name.

How can I loop through Access Field Names by index?

0 Answers