Changing Field Name in VBA

[PGZ]

Is there anyway to the change field names of a table using
VBA?

 

[Marshall Barton]

Is there anyway to the change field names of a table using
VBA?

Check Help for details, but it could be something like:

CurrentDb.TableDefs("tablename").Fields("oldanme").Name =
"newname"

 

[dchendrickson]

I don't have experience working in the older DAO methods,
but the newer ADO methods might use an approach to
changing field names in a table like this:

Public Sub TableFieldNameModify(strOld as String, strNew
as String, strTbl as String)

Dim cat As New ADOX.Catalog
Dim cnn As New ADODB.Connection
Dim tbl As New ADOX.Table

Set cnn = CurrentProject.Connection
Let cat.ActiveConnection = cnn
Set tbl = cat.Tables(strTbl)

Let tbl.Columns(strOld).Name = strNew

End Sub

WHERE:
strOld is the current field name to be changed
strNew is the desired field name
strTbl is the name of the table containing the field

Hope this is what you had in mind.

-dc

 

[Douglas J. Steele]

You've gotten a couple of answers on how to do this now. I just wanted to
remind you that if you change the field names, that doesn't change the names
in any objects linked to that table (i.e.: queries, forms, reports, etc.).

For that, you might want to look into 3rd party products like Find & Replace
http://www.rickworld.com/products.html or Speed Ferret
http://www.moshannon.com/speedferret.html

 

[PGZ]

When I tried the following, I received a "Item not found
in this collection." error message.

CurrentDb.TableDefs("Table_Struct_Chains_TEMP").Fields
("Top Tier ID").Name = "Tier 02 ID"

I also tried this:

Table_Struct_Chains_TEMP.Field("Top Tier ID").Name = "Tier
02 ID"

but I received "Object doesn't support this property or
method" error message.

Any suggestions?

 

[Marshall Barton]

When I tried the following, I received a "Item not found
in this collection." error message.

CurrentDb.TableDefs("Table_Struct_Chains_TEMP").Fields
("Top Tier ID").Name = "Tier 02 ID"

Assuming you're using DAO, double check to make sure you're
using the exact spelling of the table and field names.