Rename Tables with Parameter Prompt

[Kevin]

I tried this question in the macro section with a "not possible" answer so I
thought to try here. I would like to rename mulitple tables in one step by
appending a suffix to the end of the existing table names. However, I would
like a parameter prompt to specify what to append to the names. Is this
possible? Thank You for any effort.

 

[Douglas J. Steele]

You can write VBA code that uses the InputBox function to prompt for the
suffix, and then rename the table.

Using DAO, it would be something like:

Dim strSuffix As String
Dim strTableName As String

strTableName = "MyTable"
strSuffix = InputBox("What Suffix?", "Rename Table", "")
If Len(strSuffix) > 0 Then
CurrentDb().TableDefs(strTableName).Name = _
strTableName & "_" & strSuffix
End If

 

[Kevin]

Wow Doug, thanks alot. One Quick question before I try this (sorry I am just
starting with modules). Can I perform this for multiple table in one step or
just one table at a time?

strTableName= table1, table2, table3, etc.

 

[Douglas J. Steele]

You have to do each table individually, but you can create an array of table
names and use a loop to do them all.

Dim intLoop As Integer
Dim strSuffix As String
Dim varTableNames As Variant

varTableNames = Array("Table1", "Table2", "Table3")
strSuffix = InputBox("What Suffix?", "Rename Table", "")
If Len(strSuffix) > 0 Then
For intLoop = LBound(varTableNames) To UBound(varTableNames)
CurrentDb().TableDefs(varTableNames(intLoop)).Name = _
strTableName & "_" & strSuffix
Next intLoop
End If