Link to home
Create AccountLog in
Avatar of Murray Brown
Murray BrownFlag 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
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

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

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

Avatar of 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

can you share the codes of function: GetLinkedDBName ?

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
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
That worked. Thank very much Ryan!