Avatar of Murray Brown
Murray Brown
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Access VBA Rename backend table

Hi. I was given the following Access VBA code to rename a backend column in a split Access database solution. What similar code would I use to rename a table? Thanks

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
Microsoft AccessVBA

Avatar of undefined
Last Comment
Murray Brown

8/22/2022 - Mon
Ryan Chong

try something like:


Public Sub RenameTable(ByVal oldTableName As String, ByVal newTableName As String)
    Dim dbName As String
    dbName = GetLinkedDBName(tableName)
   
    Dim db As DAO.Database
    Set db = OpenDatabase(dbName)
    
    Dim tbl As DAO.TableDef
    For Each tbl In db.TableDefs
        If tbl.Name = oldTableName Then
            tbl.Name = newTableName
            Exit Sub
        End If
    Next
End Sub

Open in new window


to implement:


Call RenameTable("Table1", "Table2")

Open in new window

Ryan Chong

or actually can use existing Access function:


DoCmd.Rename "New Table Name", acTable, "Old Table Name"

Open in new window


DoCmd.Rename method (Access)

https://docs.microsoft.com/en-us/office/vba/api/access.docmd.rename

Murray Brown

ASKER
Hi Ryan.
I ran the following code and the linked table didn't change

    Call RenameTable("t_Honorific", "t_Titles")

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
Ryan Chong

can you share the codes of function: GetLinkedDBName ?

Murray Brown

ASKER
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

ASKER CERTIFIED SOLUTION
Ryan Chong

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Murray Brown

ASKER
That worked. Thank very much Ryan!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.