Is it possible to rename table field names?

[Talismanic]

Is it possible to rename table field names with code. I have a spreadsheet that is beeing imported from Excel and I would like to set the headers once the table is imported into Access.

I know that I can have Access assign the first row as field names but that approach will not work in this case. Any ideas?

 

[charityg]

Create a maketable query.
select field1 as newname, field2 as newname2 into newtable from oldtable;

 

[Talismanic]

Not exactly what I was looking for but it worked great, thanks!

 

[Pat Hartman]

Rather than importing into a new table, you can append to an existing table that has column names defined as you want them.

 

[David Mack]

Any reason we can't rename them via a user defined import specification file?

 

[Atomic Shrimp]

You can't set up an import spec for an Excel file.

 

[Atomic Shrimp]

Also, be warned that when you import an excel spreadsheet manually, if the first row doesn't contain field names, then the fields will be named Field1, field2...

but if you import it using code, the field names will be F1,F2...

Which can be rather annoying during the debugging process.

Mike

 

[Talismanic]

Since I delete the old table before I import the new one I used a Make Table Query with this as my SQL Statement:

SELECT Jobs.F1 AS JobNumber, Jobs.F2 AS JobName, Jobs.F3 AS Travel, Jobs.F4 AS PW INTO JobNumbers
FROM Jobs;

That way all the fields get the names that the database uses to do look ups.

 

[David Mack]

Problem solved but you could use the import spec if you imported the excel sheet as a .csv file.

 

[Talismanic]

I was shown how to do it with code so I am passing it on.

CurrentDb.TableDefs("NameOfTable").Fields("OldName").Name = "NewName"
CurrentDb.TableDefs("NameOfTable").Fields("NewName").DefaultValue = "AnyValue"

As you can see I also was able to set the defualt value. I am not 100% sure about this but I think you can do everything to a table from code as you could from design view. It looked as if most of the objects were available.