Deleting/creating table does not refresh tabledefs

[WhatTheFrick]

Using Access 2007, but the file is in 2000-2003 format.

I'm looping through Excel files and importing them one at a time. Because of the situation I'm forced into, each Excel file could have different field names, where I have standard tables that use the Excel data. So first I'm importing the Excel file into a temp table, then looping through and changing the field names to their appropriate Access field name, then updating tables. I know this is probably a bad way of doing things, but since the field names can be different from one file to the next, and be in a different order, I do still know what each field name always corresponds to, so I have a lookup table that finds the Excel field name, and changes it to its Access fieldname.

This is working the first time through. HOWEVER, when I delete the temp table after the first Excel file is done and recreate it with an import (this work too), for some reason, even though in Break Mode I can see the imported table and the Excel field names, when I refer to the tabledefs for that table (which I set to nothing and then REset upon every Excel file), it's for some reason "remembering" the field names of what I CHANGED them to the first iteration through code. It's like even though the table got deleted, recreated, and I set tabledefs to nothing and re-set it to the now-existing table, it's still remembering the PREVIOUSLY changed field names.

I do this:
1) If table exists, delete it (this works)
2) Import Excel file (works)
3) (I previously declared all following variables as correct type; tabledesfs, field, etc.):

Set tblDef = CurrentDB.TableDefs("MyTempTable")
For Each tmpField In tblDef.Fields
    strNameChange = Nz(DLookup("[AccessFieldName]", "LookupTable", "[RefID] = ReferenceVariable and [XLFieldName] = " & """" & tmpField.Name & """"), "")
        tmpField.Name = strNameChange
Next tmpField
Set tmpField = Nothing
Set tblDef = Nothing

This works PERFECTLY all the way up until my SECOND Excel file, where everything works EXCEPT that my tblDef.Fields(0).name is the field name that I changed it TO from the first loop. I can break right after I RESET tblDef, but even though the table was previously deleted, the tblDef set to nothing, and the table RECREATEd and the tblDef RESET, the tblDef.fields.names are what they were after I changed them in the previous run through this loop. I even checked to make sure that when I set tblDef to nothing, when I tried to access a field name in it I get the appropriate error that tblDef isn't set or whatever. So why is it when I reset it to the new table, it's remembering previous field names?

How can I "flush" and re-establish the tblDefs after the table is delted and then recreated?

Any help on this would be greatly appreciated, and thanks for reading.

[June7]

Are you using the Excel imports to append records or update existing records or both?

Has to be a way to accomplish the append/update without having to change field names of the temp table although the code might be just as or more complicated. How many fields are involved? The Excel files are always the same names? Can't you just link to the files?

[WhatTheFrick]

Are you using the Excel imports to append records or update existing records or both?

Has to be a way to accomplish the append/update without having to change field names of the temp table although the code might be just as or more complicated. How many fields are involved?

I use the Excel records to (eventually) Append after some other calculations/data wrangline are done on them.

There are 15 Access fields, but the incoming Excel files could have 3, 5, 20, etc. I just need to change their names after import to match the ones in Access. In my Lookup table, I know by the Excel file name what the fields are, and I have their Access equivalent. I'm not allowed to change the names in the Excel file, and I'd rather change the names after import than save the excel file as a copy, change THOSE names using a bunch of similar code in Access VBA, then delete the Excel file copy I made (which I'd have to do, because a save action is required when changing Excel in order to import into Access).

Isn't there some way to update a tabledef? I don't understand why setting it to nothing and then setting it again doesn't work.

[June7]

I don't know why the tabledef isn't working, never tried anything like this. I could suggest an alternate solution if you want one.

Again, the Excel file names are always the same, can you link to the files? How many files? Linking not essential to alternate solution but might be helpful.