Change field name using SQL

[amerifax]

I have a Access 2007 database that contains five tables. I need to make a permanent change on some of the field names. In the past we were working with dBase IV, and it was always best to use similar feels with the same name.

Example:
Permit.bld
Builder.bld

Now, I would like to change fields that share the same name.
Builder.bld to Builder.bbld.

Is there a way to change field names using SQL. Within the five tables. I probably have 20 feels or more were I would like to change the field name, just by adding the first letter of each table to the affected field.

So is there an SQL command where I can:
Change Builder.bld to Builder.bbld
Change Builder.addr to Builder.baddr
Change Builder.phone to Builder.bphone ?

Bob

 

[Pat Hartman]

There is no SQL command. You will need to use DAO (ADO may also work). You will need to loop through the fields collection of the tabledefs collection for each table. You will also have to do the same thing for the querydefs collection to fix the queries.

 

[Galaxiom]

I don't know if NameAutoCorrect is active when names are changed using DAO code but I would definitely make sure it was off bofore do this.

Another tool you would want to run over the database is the Total Deep Search from V-Tools. This will find the other references.

In fact I would be more inclined to use this tool for the whole job.

In my experience one needs a very good reason to change the names of objects in a database beause it takes a lot of retesting to make sure it is all working again afterwards.

 

[amerifax]

After 20 years of dBase files we want to make some of the changes to the structure that we limped with over the last 20 years. Especially since were switching to Access.

Bob