We help IT Professionals succeed at work.

Access VBA Rename backend table

Murray Brown
Murray Brown asked
on
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
Comment
Watch Question

View Solution Only

Ryan ChongSoftware Team Lead

Commented:

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


to implement:


Call RenameTable("Table1", "Table2")
Ryan ChongSoftware Team Lead

Commented:

or actually can use existing Access function:


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


DoCmd.Rename method (Access)

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

Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
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
Ryan ChongSoftware Team Lead

Commented:

can you share the codes of function: GetLinkedDBName ?

Murray BrownASP.net/VBA/VSTO Developer

Author

Commented: 
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

Software Team Lead
Commented:

is these worked for you?


try:


Public Sub RenameTable(ByVal oldTableName As String, ByVal newTableName As String)
    Dim db As DAO.Database
    Set db = CurrentDb
    
    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

Or:

Public Sub RenameTable(ByVal oldTableName As String, ByVal newTableName As String)
    DoCmd.Rename newTableName, acTable, oldTableName
End Sub


Both worked for me to rename linked table.

Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
That worked. Thank very much Ryan!