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

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

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