How to change field name in a table

  • Thread starter Thread starter Diego via AccessMonster.com
  • Start date Start date
D

Diego via AccessMonster.com

Hi
i am a table Mytable like this:

field1 field2 field3 .........fieldn
A B C N
A1 B1 C1 N1
.............................

and another table PARAMS that is like this

Name .......
Newfield1
Newfield2
...
NewfieldN

the goal is to change the field name of the first table with the contents of
PARAMS table (field Name).
The results should be like
Newfield1 Newfield2 New field3 .........Neweldn
A B C N
A1 B1 C1 N1
.....................................

Practically i want to rename only the name of the fields of MYtable with the
name of another table..
Any idea ?
Thank you a lot for any suggestion
diego
 
Why? I'm hard pressed to imagine such a requirement in a production
application!

In any case, you can rename fields using DAO:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs("NameOfTable")
For Each fldCurr in tdfCurr.Fields
' Figure out the new field name for each field.
fldCurr.Name = strNewName
Next fldCurr

I'll leave the "Figure out the new field name for each field." part to you.
<g>
 
Yes i can understand your perplexity for this requirement. Anyway my problem
is exactly this.
How to assign the field name automatically from the table PARAMs.
Sorry by that but i am a beginners.
BR
Why? I'm hard pressed to imagine such a requirement in a production
application!

In any case, you can rename fields using DAO:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs("NameOfTable")
For Each fldCurr in tdfCurr.Fields
' Figure out the new field name for each field.
fldCurr.Name = strNewName
Next fldCurr

I'll leave the "Figure out the new field name for each field." part to you.
Hi
i am a table Mytable like this:
Click to expand...
[quoted text clipped - 27 lines]
Thank you a lot for any suggestion
diego
Click to expand...
Click to expand...
 
If you're strictly going to be renaming Field1 of the existing table to
Field1 of the PARAMS table, you should be able to use something like:

Dim dbCurr As DAO.Database
Dim tdfExisting As DAO.TableDef
Dim tdfParams As DAO.TableDef
Dim lngLoop As Long

Set dbCurr = CurrentDb()
Set tdfExisting = dbCurr.TableDefs("NameOfTable")
Set tdfParams = dbCurr.TableDefs("Param")

If tdfExisting.Fields.Count <> tdfParams.Fields.Count Then
MsgBox "There are a different number of fields in the tables"
Else
For lngLoop = 0 To (tdfExisting.Fields.Count - 1)
tdfExisting.Fields(lngLoop).Name = _
tdfParams.Fields(lngLoop).Name
Next lngLoop
End If



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Diego via AccessMonster.com said:
Yes i can understand your perplexity for this requirement. Anyway my
problem
is exactly this.
How to assign the field name automatically from the table PARAMs.
Sorry by that but i am a beginners.
BR
Why? I'm hard pressed to imagine such a requirement in a production
application!

In any case, you can rename fields using DAO:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs("NameOfTable")
For Each fldCurr in tdfCurr.Fields
' Figure out the new field name for each field.
fldCurr.Name = strNewName
Next fldCurr

I'll leave the "Figure out the new field name for each field." part to
you.
Hi
i am a table Mytable like this:
Click to expand...
[quoted text clipped - 27 lines]
Thank you a lot for any suggestion
diego
Click to expand...
Click to expand...
Click to expand...
 
Thank you very much
i will try.
BR
If you're strictly going to be renaming Field1 of the existing table to
Field1 of the PARAMS table, you should be able to use something like:

Dim dbCurr As DAO.Database
Dim tdfExisting As DAO.TableDef
Dim tdfParams As DAO.TableDef
Dim lngLoop As Long

Set dbCurr = CurrentDb()
Set tdfExisting = dbCurr.TableDefs("NameOfTable")
Set tdfParams = dbCurr.TableDefs("Param")

If tdfExisting.Fields.Count <> tdfParams.Fields.Count Then
MsgBox "There are a different number of fields in the tables"
Else
For lngLoop = 0 To (tdfExisting.Fields.Count - 1)
tdfExisting.Fields(lngLoop).Name = _
tdfParams.Fields(lngLoop).Name
Next lngLoop
End If
Yes i can understand your perplexity for this requirement. Anyway my
problem
Click to expand...
[quoted text clipped - 28 lines]
Click to expand...
 
Back
Top