Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

ms-access

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?

like image 756
Mohamed Kamal Avatar asked Dec 22 '10 18:12

Mohamed Kamal


2 Answers

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
like image 97
Fionnuala Avatar answered Oct 15 '22 21:10

Fionnuala


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
like image 22
Ali ahmadkhani Avatar answered Oct 15 '22 22:10

Ali ahmadkhani