Well, it sounds as though your database isn't normalized (having the _name_
of the field contain information about the _contents_ of the field is
usually an indication of this), but it isn't that hard to change a field's
name:
Function RenameField(TableName As String, OldFieldName As String,
NewFieldName As String) As Boolean
' Function renames field OldFieldName in table TableName to NewFieldName
' Function returns True if successful, False otherwise
On Error GoTo Err_RenameField
Dim booStatus As Boolean
Dim dbCurrent As Database
Dim tdfCurrent As TableDef
Dim fldCurrent As Field
booStatus = False
Set dbCurrent =
Workspaces(0).OpenDatabase("C:\Data\download\temp\Tests.mdb")
Set tdfCurrent = dbCurrent.TableDefs(TableName)
Set fldCurrent = tdfCurrent.Fields(OldFieldName)
fldCurrent.Name = NewFieldName
booStatus = True
End_RenameField:
RenameField = booStatus
Exit Function
Err_RenameField:
' Err.Number = 3265 if table TableName doesn't exist, or if
' field OldFieldName doesn't exist in table TableName
If Err.number <> 3265 Then
MsgBox "Error " & Err.Description & " (" & Err.number & ")"
End If
Resume End_RenameField
End Function
HTH
--
Doug Steele, Microsoft Access MVP
Beer, Wine and Database Programming. What could be better?
Visit "Doug Steele's Beer and Programming Emporium"
http://I.Am/DougSteele/
> After encountering Excel memory limitations, I moved the database
> entirely to Access and now use Excel 2000 as the front end. I have
> encountered a problem. It would simplify things if I could create,
> delete, and rename the field name of one of my Access tables under
> program control. Creating and deleting fields is no problem, but
> everything I have tries has failed to allow me to change an existing
> field name. I am using the field name to hold information about the
> fields contents. For example the table in question holds periotic price
> information about traded securities. Field 1 holds the security symbol
> and the additional fields hold the price at a given time. The number of
> price fields is a variable. The field name consists of a character
> concatinated with the date. The lead character allows the program to
> decide if the field represents current prices or not. I have concocted
> some ways arround the problem but they are kludges.
> Any Suggestions?
> Thanks-in-advance
> John Owens
by