• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 422
  • Last Modified:

How to rename a field in a dbf file which contains records??

Can someone please explain to me whether this is possible at all? I have tried with both DAO ( as much as I hate using obsolete technologies) and ADO. The error message I get is : "Run-time error '3282': Operation not supported on a table that contains data."  When I try to add a new field with the desired name and transfer the information from the old field, I am getting the same error. I spent almost one whole day trying to find an answer to this issue. If you do have an answer to this, please include the correct version of DAO/ADO that works for you.

Sub ChangeFieldName_DAO()

Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim td As DAO.TableDef

    Set db = OpenDatabase("F:\Projects\testarea", False, 0, "DBase IV;")
    Set tbl = db.TableDefs("exported")
    Set td = db.TableDefs(tbl.Name)
    For Each fld In tbl.Fields
        If fld.Name = "num" Then
' Attempt 1 - rename field
            td.Fields("num").Name = "newname"
' Attempt 2 - create a new field an copy the records
'            td.Fields.Append td.CreateField("newname", fld.Type, fld.size)
'            db.Execute "Update " & tbl.Name & "set newname = " & fld.Name & " "
'            td.Fields.Delete fld.Name
            MsgBox "Done"
            Exit For
        End If
    Next fld
    Set fld = Nothing
    Set tbl = Nothing
    Set db = Nothing
End Sub

Sub ChangeFieldName_ADO()

Dim adoConn As New ADODB.Connection
Dim Cat As New ADOX.Catalog
Dim sFolderName As String, sDBF As String
    sFolderName = "F:\Projects\testarea"
    sDBF = "exported"

    adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & sFolderName & ";Extended Properties=dBase IV;"
    Cat.ActiveConnection = adoConn

Dim adoTable As ADOX.Table, i As Integer
    Set adoTable = Cat.Tables(sDBF)
Dim sOldFieldName As String
    sOldFieldName = "num"
Dim sNewFieldName As String
    sNewFieldName = "newname"
    Cat.Tables(sDBF).Columns(sOldFieldName).Name = sNewFieldName

On Error Resume Next

    Set Cat = Nothing: Set adoTable = Nothing
    If adoConn.State = 1 Then
        adoConn.Close: Set adoConn = Nothing
    End If

End Sub

  • 4
  • 4
  • 3
  • +1
1 Solution
This may be a workaround.
Create a new temporary table with the same fields as the old table.
Open a recordset on the old table,scan the recordset, and add each record to the temporary table.
Delete all records from the old table.
Rename the field on the old table.
Open a recordset on the new table,scan the recordset, and add each record to the old table.
Eric ShermanAccountant/DeveloperCommented:
Try this ....

Dim dbs As Database
Set dbs = OpenDatabase("C:\YourFolder\YourDatabase.mdb")

To Add a Column to your table use this ...
dbs.Execute "ALTER TABLE YourTableName ADD COLUMN YourColumnName Text(50);"

To Change a Field Name use this ...
dbs.TableDefs("YourTableName").Fields("YourFieldName").NAME = "YourNewFieldName"


valboyAuthor Commented:
Thanks for the replies!

To josephwalsh: I was trying to avoid making any temporary tables but if nothing else helps, I'll try it.

To etsherman: The code you posted is working on a table in mdb but I am trying to do it on a standalone dbf file. Maybe I can also create a temp mdb, import the dbf, delete the old dbf, change the field name, and export to the same dbf. What a mess...
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Here is a way: referencing etsherman's comment

Table a: fields a, b
a      b
x      y
z      l

In a form:
Private Sub cmdChangeName_Click()
    Dim db As Database
    Set db = CurrentDb
    db.TableDefs("a").Fields("a").Name = "aa"
End Sub

Table a: fields aa, b
aa      b
x      y
z      l

Good luck!
note: table should be closed! Not used as record source for the form.
Eric ShermanAccountant/DeveloperCommented:
valboy ...

If you put the code I provided on a Command Button as pointed out by hnasr what happens????  Do you get some type of error???

valboyAuthor Commented:
To ET and hnasr:

First of all, I want to emphasize once again that the code MUST work on a DBF file - not a MDB table. The code provided by hnasr is basically the same I used in Attempt 1 of my script ChangeFieldName_DAO. The error is that I can't change a field name if the table contains data, which is ludicrous! My dbf files will always contain data. I guess that's a limitation of the Jet driver and there is no way around it.
Eric ShermanAccountant/DeveloperCommented:
That being the case then I would go the Temp table route and export the mdb file to a dbf once all changes have been made.

dbf! Is this a linked table? Linked ables have limitations! Can you rename it in it's original environment?.
You may consult FoxPro topic!
valboyAuthor Commented:
OK, more details. I am a GIS programmer and my databases are actually geodatabases (GDB) - they have a bunch of internal system tables on top of every table I create. The dbf is not a linked table - it's just a standalone table which hosts summary results etc. If the table is inside the GDB, I have no problem renaming the field using any of the above approaches. The catch here is that the field in the table may participate in some relationships, topology, has domains, etc. - some of which may be stored in binary format in the system tables, so I don't have an easy access to them. So, if I rename such a field, then my GDB becomes corrupt. Another thing I did was exporting the GDB to XML and replacing the field name in the flat file, then importing back the XML to GDB. This turned out to be way too time-consuming - it took me > 5 min to process a 40 MB GDB and the result was a 112 MB XML file. Obviously, it's not worth the hassle if all I want is change one measly field name.

By the way, renaming a field has been popping up on GIS forums for more then 4 years now and there is still no fast and reliable solution. I thought I would be the one to finally break the bad spell :)

If there are no other ideas, I'll do the recordset. On a related note, is there any limit on the size of the recordset sitting in memory? What if I had 200,000 records? I love recordsets and have been using them extensively, but not on such a large scale.

Thanks to all for your persistence!
One idea:  One pros of views, is that you can stop worrying about column names in tables.  Create a view with new names for the fields and relate to other tables.
Hope that will help.

I tried 250,000 records recordset.  You can give ita try in your enviroment.
valboyAuthor Commented:
Hey everybody! Sorry for the long delay. That was my free-time project and lately free time was in severe shortage. I am accepting the workaround, suggested by josephwalsh, although I was trying to avoid dealing with temp files.

hnasr - unfortunately, there are no views and aliases in a standalone dbf table outside a MDB. I guess most of the people hardly ever work with dbf files anymore :)

josephwalsh, thank you for your suggestion. I was split between A and B grade. I chose good because the workaround is not as straightforward as I was hoping for. If anyone in this thread can prove me that there is no other way to do this, by quoting MS , I'll open a new thread and assign more points.



Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now