Hi:

I have a situation where I need to rename the fields in an Access table using VBA but I'm having no luck. For example I may want to rename the fields in the table from whatever they are to Field_1, Field_2 and so on.

I would appreciate any help I can get on this one.

Thanks,

Ian

Why?!

Whatever your problem is, I'm almost sure you could do something smarter... Do you know UNION?

Nope. Anybody else help?

Hi,

   Are you trying to rename them in reports or forms? If so, just change their Caption to the name you want to appear.

   If you are changing them on the fly, you can use aliases in query fields like this:

Query Field - Field_1:([RealFieldName])

   The RealFieldName of the field will show up as "Field_1" in all subsequent uses.

   You can also use VBA to do this. If you use the Form's OnCurrent Event and use this code:

  Me.TextBox_Label.Caption = "Field_1"

Hope this helps,

mmcdonal

You could also do this in a report using the Detail's OnFormat property and referring to the Lable.Caption property.

mmcdonal

Thanks for replying. I'm afraid neither case applies. I would like to be able to change actual field names in a table which is an imported, manually prepared, Excel spreadsheet and the first row has column headings which may have spaces, periods or slashes etc.
I would like to rename the field names to remove the above characters.

For example:

Default date to DefaultDate
BalanceO/s to BalanceOs
Old.address to OldAddress

and so on. I have code that runs against this table and it would save me having to go in and change the field names manually.

Dim sSQL As String
Dim cn As Connection

    Set cn = CurrentProject.Connection
    cn.CursorLocation = adUseClient

    sSQL = "EXEC sp_rename 'tblSiteFee.[Modified Date]', 'ModifiedDate', 'COLUMN'"
    cn.Execute(sSQL)
set cn = nothing
cn.close

I would change the field names manually.

mmcdonal

Despite the cynical comments you have so far received, I too have to do this regularly. All my legacy databases are currently DAO so if you have not got the library in your references, you must activate it before you run this

Function changefieldnames(oldname As String, newname As String)
'----- oldname and newname are passed to this function from
'----- wherever you are in your process. I would do it via a
'----- form, but if the field names you change are the same every
'----- time the process is run, why not store them in a table. The
'----- code you will need to pass the field names to be corrected
'----- from the table is given in Sub readinfieldnames() below.

Dim db As Database
Dim tdf As TableDef
Dim n As Object

Set db = CurrentDb
Set tdf = db.TableDefs("tablename")
For Each n In tdf.Fields
    If n.Name = oldname Then n.Name = newname
Next n

Set tdf = Nothing
Set db = Nothing

End Function

Sub readinfieldnames()
Dim rst_data As Recordset
'------ Assumes you have a table with 2 columns. Column 1 contains
'------- downloaded field names, column 2 has the names you want
Dim oldfieldname As String, newfieldname As String

Set rst_data = CurrentDb.OpenRecordset("tbl_of_field_names")

With rst_data
    .MoveFirst
    Do Until .EOF
        oldfieldname = .Fields(0).Value
        newfieldname = .Fields(1).Value
        changefieldnames oldfieldname, newfieldname
        .MoveNext
    Loop

 End With
 Set rst_data = Nothing

End Sub


I did this from memory so there may be one or two syntax errors. Sorry in advance. Hope this helps


Alan T
[email protected]

This code might be a little bit more flexable

Sub changefieldnames()

Set db = CurrentDb()
For Index = 0 To db.TableDefs.Count - 1
    If Left(db.TableDefs(Index).Name, 4) <> "MSys" Then
        tablename = db.TableDefs(Index).Name
        MsgBox (tablename)
        Set tdf = db.TableDefs(tablename)
        For Each n In tdf.Fields
            oldname = n.Name
            n.Name = Replace(oldname, "xxx", "yyyyyy")
        Next n
    End If
Next
Set tdf = Nothing
Set db = Nothing

End Sub