Changing Table Field Name with Code....Possible?! 1

[DCBBB]

Is there a way to change a tables field names with code? I would like to automate a report based on a crosstab query, where the heading will change constantly. Because there are over 400 possible column haedings, using the columns property is out. I tried creating a report with unbound fields and assigning headings and data at runtime, but because this report has so many calculations, it got way too hairy getting the calculations to work. So, I want to try to attack this problem by using a report with bound columns, which requires me to have fixed column names in my crosstab. To do this, I could do a make table off of my crosstab and then change the table fieldnames. But how do I automate this change with code? Is this possible? Thanks!
D

 

[KF]

Yes, here is a sample of code I used to change the size of a field that currenly had data in it. You can modifiy this code to work for you. You must first create a temp field and move your data into it. Then create the new field (new name)and move your data into it. Then drop the old field.

Public Function ChangefieldSize()
On Error GoTo Err_ChangefieldSize

Dim MyWorkspace As Workspace, MyDatabase As DATABASE
Dim MyField As Field
Dim MyTableDef As TableDef


Set MyWorkspace = DBEngine.Workspaces(0) ' Get current workspace.
Set MyDatabase = MyWorkspace.OpenDatabase(Forms!frmUpdate!txtPath) ' Open database.
Set MyTableDef = MyDatabase![tblTasks]

Set MyField = MyTableDef.CreateField("temp", dbText, 255)

On Error GoTo Err_ChangefieldSize
MyTableDef.Fields.Append MyField ' Append field to collection.

'move the values
MyDatabase.Execute "UPDATE DISTINCTROW tblTasks SET tblTasks.temp = [tblTasks]![TaskType];"

'drop the old field
MyDatabase.Execute "ALTER TABLE tblTasks DROP COLUMN TaskType;"
MyDatabase.TableDefs.Refresh

'change the origional field size and add it back to the table
Set MyField = MyTableDef.CreateField("TaskType", dbText, 25)
MyTableDef.Fields.Append MyField

'move the values back to the edited field
MyDatabase.Execute "UPDATE tblTasks SET tblVolunteerTasks.TaskType = [tblTasks]![temp];"

'delete the temporary field
MyDatabase.Execute "ALTER TABLE tblTasks DROP COLUMN temp;"
MyDatabase.TableDefs.Refresh

Exit_ChangefieldSize:
Exit Function

Err_ChangefieldSize:
Exit Function

End Function

Hope This Helps,
KF

 

[DCBBB]

Thanks! I've been wracking my brain trying to figure this out and this solution is so simple I don't know why I didn't think of it! Thank you, thank you, thank you!