Rename tables with code 2

[PCX]

I have a database with 100+ tables. Each table was imported with "TABLE_" as a prefix and the actual table description follows that.

Is there a simple code to cycle through each table in the database and remove the prefix from the name?

(i.e. if the table name is "TABLE_FORMDATE" I want it to be renamed "FORMDATE&quot

 

[tunsarod]

Put this is a module and run it:

Public Sub RenameTables()
Dim dbs As Database, tdf As TableDef
Dim tblName As String

Set dbs = CurrentDb

For Each tdf In dbs.TableDefs
tblName = tdf.Name
If tblName Like "TABLE_*" Then
DoCmd.Rename Mid(tblName, 7), acTable, tblName
End If
Next tdf

Set dbs = Nothing

End Sub

 

[raskew]

This should do it. Copy/paste the following to a new module then, from the debug window, type: ? RenameTable(&quot;table_&quot <enter>

The debug window will display the number of renamed tables, then switch to the database window to view the changes.

Function RenameTable(pPrefix As String) As String
'*******************************************
'Name:      RenameTable (Function)
'Purpose:   Remove pPrefix from all tablenames
'Inputs:    From debug window: ? RenameTable("table_")
'Output:    View database window | Tables
'*******************************************

Dim db As Database
Dim td As TableDef
Dim n As Integer, i As Integer

Set db = CurrentDb
i = 0
n = Len(pPrefix)

For Each td In db.TableDefs
   If Left(td.Name, n) = pPrefix Then
      td.Name = Mid(td.Name, n + 1)
      i = i + 1
   End If
Next td

RenameTable = LTrim(Str(i)) & " tables were renamed."
db.Close
Set db = Nothing

End Function

 

[PCX]

Perfect.

Both examples changed the table names. The one by raskew changed the prefix including the &quot;_&quot; (underline) character while the one by tunsarod changed the prefix but left the underline. I'm sure with a little tweaking, that could also be resolved.

Thanks for the quick response.

 

[raskew]

The two routines are virtually identical with the exception that Tunsarod provided a solution specific to your example while I tried to provide a more generic one which would work with any suffix.

Bob

 

[LikeThisName]

hey fellas

how would i automate renaming forms

instead of tableDef where should i look. thanks