Hi,
Before I create a graph in Access I should like to rename the field names,
in the table used for the graph in mind, in accordance to new names stored in
another table. I hope to do this by first make a copy of the actual table and
then run some Visual Basic code to do the renaming. E.g "old_name" becomes
"new_name".
Any help to guide how write this VB-code should be appreciated!
I recently wanted to do the same thing. It's amazing how many people will tell
you anything *but* how to change field names! Anyway, here's what I came up
with . Change "Cobol Field Name" to whatever the correct name is for the field
that has the new names. Clear as mud? I created a form with a couple of text
boxes to enter the table names and a command button from which to call the
function. Maybe one day I'll change the text boxes to list boxes so I can just
pick the tables.
HTH,
RD
Function fChangeFieldNames(sTblA As String, sTblB As String) As Boolean
' sTbl will be the data table, sTblB will be the field name table
On Error GoTo ErrorHandler
Dim db As DAO.Database
Dim tblDefA As DAO.TableDef
Dim rs As DAO.Recordset
Dim fldDef As DAO.Field
Dim i As Integer
Set db = CurrentDb
Set tblDefA = db.TableDefs(sTblA)
Set rs = db.OpenRecordset(sTblB)
rs.MoveFirst
For i = 0 To tblDefA.Fields.Count - 1
Set fldDef = tblDefA.Fields(i)
fldDef.Name = rs.Fields("Cobol Field Name").Value
rs.MoveNext
Next i
fChangeFieldNames = True
ExitPoint:
Set rs = Nothing
Set fldDef = Nothing
Set tblDefA = Nothing
Set db = Nothing
Exit Function
ErrorHandler:
Debug.Print Err.Number & ": " & Err.Description
' Stop
fChangeFieldNames = False
Resume ExitPoint
End Function