Avatar of Murray Brown
Murray Brown
Flag 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

Microsoft AccessVBA

Avatar of undefined
Last Comment
Murray Brown

8/22/2022 - Mon
John Tsioumpris

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 Brown

ASKER
Sorry John. I have added the code in the original question
John Tsioumpris

Have you checked the code and link i posted.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Fabrice Lambert

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Scott McDaniel (EE MVE )

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 )

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 Tsioumpris

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Fabrice Lambert

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

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 Brown

ASKER
thanks very much
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck