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

About Garry Robinson

He is the guy who originally put this Smart Access web site together. Head to www.gr-fx.com.au for more about him and his company. His main claim to fame is winning the Microsoft MVP award for services to MS Access from 2006 to 2018.
This entry was posted in Design and Tables. Bookmark the permalink.

Leave a Reply

Your email address will not be published.


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.