How do I change the name of a field in a table (1 Viewer)

[aziz rasul]

How do I change the name of a field in a table. Say that the name of the first field is [Employees ID] and I want to change it to, say [Employees].

I have tried the following: -

rst.Fields(0).Name = "Employees"

However it comes with a run-time error of 3219 (Invalid operation).

Is this because it's a read only property and hence can't be changed programatically?

 

[Ally]

Why can't you just change the name of the field in design view of the table? You'll need to change any occurance of that field in queries etc.

 

[aziz rasul]

For reasons that ere too compliacted, I need to achieve this by using VBA.

 

[David R]

Are you going to change all the query references, other VBA code, etc, as well?

I'm really confused as to what you're trying to accomplish. Can you give an example?

 

[aziz rasul]

Here's the snippet of code that I'm working on: -

'values from the xls file
OneOne = obxls.Cells(1, 1).Value
OneTwo = obxls.Cells(1, 2).Value
OneThree = obxls.Cells(1, 3).Value
TableName = "Period " & Me!Period & " Year " & Me!Year & " Month Actuals"

Set rst = dbs.OpenRecordset(TableName)
'rst.Fields(0).Name = OneOne
'rst.Fields(1).Name = OneTwo
'rst.Fields(2).Name = OneThree

DoCmd TransferSpreadsheet .....

 

[The_Doc_Man]

The bad news. You cannot do this from the recordset.

The good news - you can do this from the tabledef.

In your VBA, you have to find the TableDefs collection, then find the specific table. If you declare a variable to be a tabledef object, you can then set the variable to point to this table.

Once you have found the tabledef for your targeted table, you have to examine the Fields collection. Find the right field by its old name to find its index. Then you use its index to redefine the {tabledefs-object}.Fields(index).Name = new name.

Before you can use the new name, you have to close the tabledef.

Look up the TableDefs collection and Fields collection as starting points. The examples should help you find what you need.

 

[aziz rasul]

Thanks for putting me in the right direction.