How to rename a field in a dbf file which contains records??
Posted on 2006-05-02
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.
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
Set fld = Nothing
Set tbl = Nothing
Set db = Nothing
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