Renaming Tables - and SQL Linked Tables

<< Click to Display Table of Contents >>

Navigation:  Design and Tables  >

Renaming Tables - and SQL Linked Tables

 

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
Next
 
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
Next
End Sub