Programmaticaly rename a column in Access table after import from Excel

Question

Is there a way to programmatically rename the 1st column of a table in Access? Situation: after importing an Excel file into Access, I always need to rename the 1st column which always bears a different column name and manually rename it into F1, it would be much easier if this could be done programmaticaly. Is there an easy way to accomplish that using VBA?

ashareef ashareef · Accepted Answer · 2013-07-23T15:56:06

Basically open an access application object then you're essentially renaming it as you would within Access vba.

Dim appAccess As Access.Application
Set appAccess = New Access.Application
With appAccess
    .OpenCurrentDatabase "C:\...\DatabaseName.accdb"
    .CurrentDb.TableDefs("Table1").Fields(1).Name = "F1"
    .CloseCurrentDatabase
End With

Replace "Table1" with the name of your table and .Fields(1) refers to the first field, .Fields(2) refers to the second etc.

Note: Make sure you set your references to Access Object Library (found in Tools > References). It'll have a name similar to "Microsoft Access 12.0 Object Library"

Programmaticaly rename a column in Access table after import from Excel

1 Answers