Rename column using Access 2000 VBA 3

[vamoose]

Good morning, I am importing an Excel 2000 document into Access 2000. I am able to delete the unused columns with the following code:

DoCmd.RunSQL "Alter Table Imported Drop Column [F1]"

Now with [F1] deleted the next column I would like to keep is currently named [F2]. I would like to give this column a more maningful name like [Coil]. Is it possible to rename a column using something like this:

DoCmd.RunSQL "Alter Table Imported Rename Column [F1], [Coil]"

Thank you for any and all suggestions.

 

[LarrySteele]

vamoose,

Much to my suprise, looks like you can't use ALTER TABLE/ALTER COLUMN to change a column name. Here's the info from Microsoft: ALTER TABLE Statement (Microsoft Access SQL).

HTH,
Larry

 

[GrahamHills]

You cannot rename a field with SQL in Access, but you would be able to:-

1. Add a column
2. Perform an Update to the new column name
3. Then Delete the Original Column

 

[PHV]

Or modify the DAO.Field.Name property.
You'll use the DAO.TableDefs collection.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vamoose]

Would it be possible for you to provide an example. Please and thank you.

 

[vamoose]

After hours of searching here is the answer:

CurrentDb().TableDefs("myTable").Fields("myOldColumn").Name = "myColumn"

Thanks to RoyVidar and dBforums

Hope this helps someone.