How to rename tables in microsoft access using vba / macro?

Question

I have an ms access database with 24 tables. I need to rename the tables every day from

table 1 --> table 1backup

any ideas will be appreciated.

Is it doable with VBA?

Galactometer

Sounds like a hideous design. Why do you think you need to do this? – Scleroprotein

Answer 1

You can:

Dim tdf As TableDef

For Each tdf In CurrentDb.TableDefs
    If Left(tdf.Name, 4) <> "MSys" Then
      tdf.Name = tdf.Name & "_backup"
    End If
Next

Bamberg

How might this be adapted to rename a single table? – Boser

CurrentDb.TableDefs("mytablename").Name = "mynewtablename" – Outdo

Answer 2

I would suggest below code. It would replace newly renamed table, with the existed table, if already has existed with the new table name:

Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
    If Left(tdf.Name, 7) <> "backup_" Then

        Dim newTableName As String
        newTableName = "backup_" + tdf.Name

        DoCmd.SetWarnings False
        DoCmd.Rename newTableName, acTable, tdf.Name
        DoCmd.SetWarnings True
    End If

Next

Bindman