Issue using Access DoCmd.Rename on table: linked names not renamed

Question

Summary: why might Docmd.Rename on a table result in tables that don't change name over a Link from another DB?

I'm trying to fix up an old database that needs TLC. Part of this is deleting lots of unused cruft, amongst which are some tables. The first part if a VBA procedure that calls DoCmd.Rename on these tables, renaming with DELETE_ prepended.

The "deletes" appear to work fine - but when I try to reference tables from another DB using the Linked Table manager, no renames have happened at all. If I go back and load that DB, the table names are changed.

Is it best to use TableDefs().Name to rename? Is that a better method? I'd assumed an "official" way like Rename would be better.

I'm using Access 2007 on Win7/64. The files are in MDB format.

Answer 1

Do you wish to rename the tables in the linked database? If so, you can use OpenDatabase to reference the linked Access database. You might try something on the lines of:

Dim dbLink As DAO.Database
Dim dbCurr As DAO.Database
Dim ws As DAO.Workspace
Dim rst As DAO.Recordset
Dim tdf As TableDef

Set ws = DBEngine.Workspaces(0)
Set dbCurr = CurrentDb

For Each tdf In dbCurr.TableDefs
    sConn = tdf.Connect
    sSource = tdf.SourceTableName
    sName = tdf.Name

    If InStr(sConn, ";DATABASE=") > 0 Then
        strdb = Mid(sConn, InStr(sConn, ";DATABASE=") + 10)

        If InStr(sConn, "PWD") > 0 Then
            sPWD = Left(sConn, InStr(sConn, ";DATABASE="))
        Else
            sPWD = vbNullString
        End If

        If Dir(strdb) <> vbNullString Then
            Set dbLink = ws.OpenDatabase(strdb, False, False, sPWD)
            dbLink.TableDefs(sSource).Name = "DELETE_" & sSource
        End If
    End If

Next