I'm developing a database that requires data from many, massive Excel files that contain no header records. I need to link to these files, but I need meaningful field names rather than F1, F2,...F193. For various reasons, I do not want to import the data, and I'm not allowed to edit the source files to insert a header row.
For reasons I can't fathom, Access doesn't allow creation and storage of a link specification as it does for import specs. So my initial solution was to programmatically rename the fields after successfully linking to the file, using a table of stored field names...
Dim tdf as TableDef
Dim fld as Field
Set tdf = CurrentDB.TableDefs("LinkedExcelTable")
For each fld in tdf.Fields
fld.Name = DLookup("NewName", "tblFieldNames", "OldName = '" & fld.Name & "'")
Next fld
...which works for normal tables, but throws the error "Operation not supported on a linked table". (Again, TableDef is stored internally within the database, so not sure why it can't be manipulated for a linked table, but that's a rant for another time).
Anyway, any suggestions for a different approach to accomplish what I'm trying to do here?