Navigation:  Tables >

Renaming Tables - and SQL Linked Tables

Previous pageReturn to chapter overviewNext page


Renaming a Bunch of Tables

I am working on a conversion database that requires linking to lots of tables in lots of databases with exactly the same tables. I then need to rename the linked tables to remove the Suffix of 1 (that appears on all linked tables that are the same) and give the new linked tables a new prefix . Here is the code to do that
Private Sub cboLive_Click()
Dim tbl As DAO.TableDef
For Each tbl In CurrentDb.TableDefs
 If Right$(tbl.Name, 1) = "1" Then
   tbl.Name = "MyPrefix_" & Left$(tbl.Name, Len(tbl.Name) - 1)
 End If
End Sub

Code to strip out the DBO prefix on SQL Server linked tables

and for people doing conversions to SQL Server


Private Sub cmdRenameTables_Click()
'Code to move through the (linked) tables that have been created
'with a SQL server upsize and rename back to their original name
'designed for the DNO owner names
Dim tbl As dao.TableDef
For Each tbl In CurrentDb.TableDefs
  If Left$(tbl.Name, 4) = "dbo_" Then
    tbl.Name = Mid$(tbl.Name, 5)
  End If
End Sub