I am building a tool for a user who gets csv file report exports from a database, but they are missing the header row. By header rows, I mean the first row of the excel/csv file that contains the field name/column name. I'll call it a field name.
The same 50-100 reports export each day/week/month. I've been asked to set up an MSAccess tool that will automate the function of inserting the field names. I will need to store a header record (file? table?) of each report's field names. Each report has a unique number assigned that will be the link between the report and the header record. I'll build an entry form for creating a new header record, and for setting up the relationship link to it's report. That's where it gets foggy. I can't save the header as a text field because it will often exceed the 255 char limit of this data type. The my first ideas for how to proceed are:
1) Have the user enter a row (in a unique table) for each column of the report. Then save the header out as a unique table, probably using the report number for the table name.
2) Just like above, have the user enter a row for each column of the report, but then save it out to an excel file, with each row becoming a column in the excel file. Would make it easy for the next step of inserting it into the report.
3) Create and export a text file and save each column name with a delimiter comma. Then call it up and read it back in with a loop to insert as field names.
I'd like to bounce this off someone to see if there is a better idea out there that I haven't considered. Any thoughts?