Trap and get unknown field name during append query (1 Viewer)

We receive daily json files via api that are then converted to csv file. Since I figured out how to use access vba to do find and replace in excel, the vba converts it to excel format in order to edit the header row (removing excess underscores or invalid chars in field name). Then use sql to append into an access table.
Every so often, the file contains an additional field and unfortunately, the vendor does not inform us of this (or if they do, I'm not on or aware of such a notification list), so when the vba runs the append query and a new field is added it generates an error. This affects all future data retrievals in that if we need to retrieve data from an earlier time period, it too will contain the new field(s).

I identified the issue by manually running the query, which produced the attached message informing me which field is missing. Does anyone know how I can trap for this and get the unknown (new) field name. I was thinking once I have that, I can use vba to modify the tabledef and append the new field name to the table so that the code can continue running without user intervention.

Do you have error trapping in your procedure? Have you determined the error number?

When you do that edit on the header row, you have the chance to compare each column header against the values you expect based on your table. If you do that edit BEFORE you do any more importing, that is your place to validate & check for a new field. Doing the check that way prevents you from taking an error trap at all.

I concur with your opinion that your data source changed and that the change is the source of your error.

Thanks Rural Guy. I added an error trap and found it to be 3127. I then used a string function to "tease out" the field from the err.description. Next, I'll see if I can then use that to programmatically add the field to the table.

The_Doc_Man. How would I go about trying your suggestion. I'm hoping not to hard code the field names as there are several data layouts with different headers going to different access tables.

This is the code I'm doing to clean up the header.

IF the headers of the spreadsheet are in fact the column names (after your editing) then you can look through the Fields collection of the target table to see if that name appears in the table as a field name.

I'm a bit rushed at the moment so can't write up a tester for you, but you could try to search the forum for "How to step through a collection" and write something that if you give it the table name and a string, it would return true or false depending on whether that string matched a field name for that table.

If the converted headers are NOT field names, then of what value are they? (I.e. why bother to convert them if they are not field names?)

The headers after editing are the column names. Would your suggestion need me to import the excel data into a temp table first in order to capture the column names? Currently, I'm directly querying and appending which saves a step or two of not having to import or link the data.

You could also open the spreadsheet using an Excel Application Object to read the columns. There are articles in this forum about how to do that. You COULD import the Excel data into a new temp table and tell it "No, the first row is NOT the field names." Then just use a recordset to open the temp table and read the first row. You can use recordset.Fields to see the fields created by the import process. You could test recordset.Fields.Count to see how many fields you had. In the case I described, they would be Field1, Field2, Field3, ..., fieldN. (I don't THINK the import would start with Field0 but I've been known to be wrong before.) There are many ways to approach this problem, but doing it either by pre-import and pre-scan or by using an Excel Application Object, you can preview the column headers and test for having a new one.

Was able to code the append field to table for error 3127, so for now will go that route rather than trying to compare fields and open recordsets etc.

Feel a bit embarrassed that I didn't have the error trap set up properly to begin with.