Link to home
Start Free TrialLog in
Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Access VBA Rename column in backend database

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

Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

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

Avatar of Murray Brown

ASKER

Sorry John. I have added the code in the original question
Have you checked the code and link i posted.
ASKER CERTIFIED SOLUTION
Avatar of Fabrice Lambert
Fabrice Lambert
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

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).

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")


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
@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.

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).                      

thanks very much