We help IT Professionals succeed at work.

Access VBA Rename column in backend database

Murray Brown
Murray Brown asked
on
Hi

I am using the following code given  to me by an expert to add a column to a linked Access table. How would I alter the code if I wanted to rename an existing column?

Thanks

Sub Add_Column_to_Backend(ByVal oTable As String, ByVal oColumn As String, ByVal oType As String)
    
    Dim strDbName As String 'Database name
    strDbName = GetLinkedDBName(oTable)
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim blnFieldExists As Boolean
    ''Set dbs = CurrentDb()
   
    Set dbs = OpenDatabase(strDbName)
    Set tdf = dbs.TableDefs(oTable)
    
    For Each fld In tdf.Fields
        If fld.Name = oColumn Then
            blnFieldExists = True
            Exit For
        End If
    Next
    
    If Not blnFieldExists Then
        If oType = "Date" Then
           Set fld = tdf.CreateField(oColumn, dbDate)
           fld.DefaultValue = "=Now()"
        ElseIf oType = "Text" Then
            Set fld = tdf.CreateField(oColumn, dbText)
        Else
            Set fld = tdf.CreateField(oColumn, dbText)
        End If
       
        tdf.Fields.Append fld
        MsgBox "New column & '" & oColumn & "' added to table '" & oTable & "'"
    End If
    
End Sub

Public Function GetLinkedDBName(TableName As String)
    Dim db As DAO.Database, Ret
    On Error GoTo DBNameErr
    Set db = CurrentDb()
    Ret = db.TableDefs(TableName).Connect
    GetLinkedDBName = Right(Ret, Len(Ret) - (InStr(1, Ret, "DATABASE=") + 8))
    Exit Function
DBNameErr:
    GetLinkedDBName = 0
End Function

Open in new window

Comment
Watch Question

John TsioumprisSoftware & Systems Engineer
Distinguished Expert 2019

Commented:
No code is attached
If you want to perform rename via code you need Jet SQL
ALTER TABLE Employees ALTER COLUMN ZipCode TEXT(10)

Open in new window

Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Sorry John. I have added the code in the original question
John TsioumprisSoftware & Systems Engineer
Distinguished Expert 2019

Commented:
Have you checked the code and link i posted.
Consulting
Distinguished Expert 2017
Commented:
Pure VBA version:
Public Sub RenameColumn(ByVal tableName As String, ByVal oldName As String, ByVal newName As String)
    Dim dbName As String
    dbName = GetLinkedDBName(tableName)
    
    Dim db As DAO.database
    Set db = OpenDatabase(dbName)
    
    Dim tdf As DAO.TableDef
    Set tdf = db.tabledefs(tableName)
    If (ExistInCollection(oldName, tdf.Fields)) Then
        Dim field As DAO.field
        Set field = tdf.Fields(oldName)
        
        field.Name = newName
    End If
End Sub

Public Function ExistInCollection(ByVal key As String, ByRef col As Object) As Boolean
    ExistInCollection = ExistInCollectionByVal(key, col) Or ExistInCollectionByRef(key, col)
End Function

Private Function ExistInCollectionByVal(ByVal key As String, ByRef col As Object) As Boolean
On Error GoTo Error
    Dim item As Variant
    item = col(key)
    ExistInCollectionByVal = True
Exit Function
Error:
    ExistInCollectionByVal = False
End Function

Private Function ExistInCollectionByRef(ByVal key As String, ByRef col As Object) As Boolean
On Error GoTo Error
    Dim item As Variant
    Set item = col(key)
    ExistInCollectionByRef = True
Exit Function
Error:
    ExistInCollectionByRef = False
End Function

Open in new window

Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:

Obviously renaming a Field in your database table could result in issues with objects that are dependent on that Field. You'd need to replace all instances of the old Field with the new Field in your database. 


There's a new Find and Replace out that seems to be pretty robust:


https://accessdevtools.com/product/find-and-replace/?wmc-currency=USD


Seems like a good replacement for the older Rick Fisher product (that seems to no longer be in development).

Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:

Also, I don't think you can rename a column with the ALTER COLUMN DDL syntax. I believe you can only alter datatypes.


You could add a new column, update the new column with the contents of the old, and then drop the old column:


ALTER TABLE YourTable ADD NewColumn Text(50)

UPDATE YourTable SET NewColumn=OldColumn

ALTER TABLE YourTable DROP OldColumn


Or just use DAO:


Dim tbl As DAO.TableDef

Set tbl = Currentdb.TableDefs("YourTable")

tbl.Fields("OldColumn") = tbl.Fields("NewColumn")


John TsioumprisSoftware & Systems Engineer
Distinguished Expert 2019

Commented:
Well @Scott is right
but of course you can add a column ..
ALTER TABLE Employees ADD COLUMN ZipCode2 TEXT(10)
Update the new Column from the Old column
DROP the old column
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
@Scott:
To my knowledge, dependant objects (I mean objects living under the Field object) are not an issue, they'll update accordingly.

But if you're thinking about objects or code using the Field, that's a different issue (more related to side impacts and debugging) wich should be taken into consideration before renaming.

As for adding a new column, copying the old one, deleting the old one via SQL, that doesn't look efficient to me.
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:

I'm referring to objects, like a Form that has a control on it with that field as the ControlSource. If you rename that field, you'd have to modify the ControlSource value for that control to reflect the new name.


As for adding a new column, copying the old one, deleting the old one via SQL, that doesn't look efficient to me.          


I'm pretty sure that's what SQL Server and almost every other database does behind the scenes. I haven't run a trace (and I'm not sure a trace would even show that), but there's ample evidence to support it online (at least for DBs like MSSQL, MySQL, Oracle, etc).


That said, the DAO process seems to be simpler (at least to me).                      

Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
thanks very much