Dustin said:
I have two tables that containg over 200 fields each that
need to have their field name changed. Currently there
are spaces between words in the field name but i need to
replace those with an underscore (Ex. field 1 to
field_1). Is there anyway to do this programically? If
so it would be a huge help as i would have to do so
manually.
Thank you,
Dustin
Here's a quickie routine to replace any string in the field names in a
table with another string. You can easily use it to replace all " "
with "_".
'----- start of code -----
Sub RenameTableFields( _
TableName As String, _
FromText As String, _
ToText As String, _
Optional CompareOption As Integer = vbTextCompare)
On Error GoTo Err_Handler
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strTableName As String
Set db = CurrentDb
Set tdf = db.TableDefs(TableName)
For Each fld In tdf.Fields
fld.Name = Replace(fld.Name, _
FromText, _
ToText, , , _
CompareOption)
Next fld
Set tdf = Nothing
Exit_Point:
Set db = Nothing
Exit Sub
Err_Handler:
On Error Resume Next
MsgBox "Table '" & TableName & "'" & vbCr & Err.Description, _
vbExclamation, "Error " & Err.Number
End Sub
'----- end of code -----
Suppose you have a table named "MyTable". You might enter this line in
the Immediate Window:
RenameTableFields "MyTable", " ", "_"
and all spaces in the field names will be replaced by underscores.
Not that this will invalidate references to those field names that exist
elsewhere in the database; in forms or queries, for example. Fixing
all those references is a much bigger job.
