Quote Originally Posted by OBP
Can we take one step back here?
Why does it matter what order the Fields are in?
Do the Field names change, or don't you have field names for the Import File?
What is the Structure of the Import File, does it have "Headings" or "Field Names"?
Normally I import the data to a Temporary Table and then Append the data in to the correct Table using a Query. But I can normally find names for the Fields in the Temporary Table.
Good morning! Or Lunch here in Sweden.

Normally I also import raw files into a temp table before moving the data along.
This time it is a little trickier for me though. To specify what the import file looks like:
The file does have a first row containing Headers.
The headers/fields change order every time. (but the names are the same, just different order)

I have tried the usual way I normally do it; Creating a import specification and then using it to automate the import process thru a macro which first drops the old import table then creates a new one while importing the new raw file.
The problem is though when importing automatically the data ended up in the wrong colums/fields. For example the Contents of the field "Status" ended up in "Description". Basically the import always took the fields exactly how the were specified when I created the import specification.

This is why I attempted to find a different way of doing it;
1. Always import the file with headers "Field1", "Field2" etc.
2. Then read the text of the first row (headers).
3. Concenate these headers into a table with two fields, oldfieldname and newfieldname.
4. Use a bit of VB code that reads the table which is specifying the old and new header names and do the renaming of the imported table.

Maybe i'm going at it wrong. But one thing is clear the setup I've come up with works. If I just could get the function to run from a macro or a button on a form.



Quote Originally Posted by OBP
I think I can get your Module to work by removing the arguements that it needs to be passed to it. In actual fact you are trying to use it incorrectly as it is designed to Rename 1 Field and you want to rename 52 of them, although how you know what order the Imported ones are compared to the names in your table I am not quite sure.
I can certainly loop through the names in the Table but how can I match them up to the names in the Imported Table?
I tried just removing the argument statement from the function as so:
But when I run it then in the VB Editor, nothing happens at all.
[vba]Function changefieldnames()
'----- oldname and newname are passed to this function from
'----- wherever you are in your process. I would do it via a
'----- form, but if the field names you change are the same every
'----- time the process is run, why not store them in a table. The
'----- code you will need to pass the field names to be corrected
'----- from the table is given in Sub readinfieldnames() below.
Dim db As Database
Dim tdf As TableDef
Dim n As Object
Set db = CurrentDb
Set tdf = db.TableDefs("trans1_SMT")
For Each n In tdf.Fields
If n.Name = oldname Then n.Name = newname
Next n
Set tdf = Nothing
Set db = Nothing
End Function[/vba]


Quote Originally Posted by OBP
In actual fact you are trying to use it incorrectly as it is designed to Rename 1 Field and you want to rename 52 of them, although how you know what order the Imported ones are compared to the names in your table I am not quite sure.
I can certainly loop through the names in the Table but how can I match them up to the names in the Imported Table?
I do know the order of the of the field names because of the way I created my Union query, It looks like this;
SELECT "Field1" AS oldfieldname , Field1 AS newfieldname from 2_1_1_read_header_row_from_import
UNION SELECT "Field2", Field2 from 2_1_1_read_header_row_from_import
UNION SELECT "Field3", Field3 from 2_1_1_read_header_row_from_import
-- and it goes on for 52 fields.

This is what I append to the table with oldfieldname and newfieldname which looks like this in a dataview after its been filled:
oldfieldname | newfieldname
Field1 | SR #
Field10 | Status
Field11 | Created
Field12 | Created By
Field13 | Follow-Up Time