Can I force an append query in VBA?

[bmac]

I would like to know if I can force an append query in Access VBA to continue if it encounters a field in the source table that does not exist in the target table and just ignore that field.

My dilemma is that I am trying to import over 900 tables that were downloaded from the internet. They represent data from multiple schedules over multiple quarters. For example:
Schedule A 03-31-10
Schedule A 12-31-09
.
.
Schedule A 03-31-04
Schedule B 03-31-10
Schedule B 12-31-09
.
.
Schedule B 03-31-04

Since the source schedules have changed numerous times over the past five years and will likely continue to change in the future, there are fields in older tables that do not exist in newer tables and vice versa.

I am really only interested in fields that are present in the newest tables. So, I wrote some code to loop through each file starting with the newest ones and import them to Access. For the newest files, Access makes a table that contains each field from the new files. Subsequent imports append to these tables. That works great except that when Access gets to a field in an older file that does not exist in the target table, it throws a terminal error saying the field does not exist.

The reason I can not just use a SELECT statement that spells out each field from the newest tables is that six months down the road, there may be a new field added that I want. And, I want to avoid having to edit the SELECT statement each time a new field is added because there are about 40 different schedules.

My ideal solution would be to tell Access to "import the table and if you get to a field that does not exist in the target table, just ignore it and keep going". Wishful thinking?

I would appreciate your thoughts on this!

[vb5prgrmr]

Okay, so you are using the Open Statement to open these files and read them in with the Line Input Function? If so, you should be able to know how many fields there are and if you know that, you should be able to do an alter table statement to add a field if needed...



Good Luck

[bmac]

@vb5prgrmr
vb5prgrmr,
I am actually just importing them at this point. After the first import of Schedule A which creates a table, each subsequent import of Schedule A files appends to the new table. That is where the problem has been.

But, your answer was definitely right on! What I decided to do was add the fields in code following the first import. There aren't too many so it seems to be working perfectly.

Thank you for the input!