Problem with Rename Tables using Prompt

[Kevin]

I am having problems with some DAO code that renames tables by appending a
suffix, that is prompted for. After I supply the suffix to the input box,
only the first table in the array is renamed and then I get a error message.
It is renaming using by using only the suffix and not appending to existing
table name (can't have duplicate table names):

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

 

[Roger Carlson]

You never declare or assign a value to strTableName. (You should set Option
Explicit at the top of the module.)

Either do this:
For intLoop = LBound(varTableNames) To UBound(varTableNames)
strTableName = varTableNames(intLoop)
CurrentDb().TableDefs(strTableName).Name = _
strTableName & "_" & strSuffix

or
For intLoop = LBound(varTableNames) To UBound(varTableNames)
CurrentDb().TableDefs(varTableNames(intLoop)).Name = _
varTableNames(intLoop) & "_" & strSuffix

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Marshall Barton]

I am having problems with some DAO code that renames tables by appending a
suffix, that is prompted for. After I supply the suffix to the input box,
only the first table in the array is renamed and then I get a error message.
It is renaming using by using only the suffix and not appending to existing
table name (can't have duplicate table names):

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

What is the strTableName variable doing in the loop?

SHouldn't if be:
CurrentDb().TableDefs(varTableNames(intLoop)).Name = _
varTableNames(intLoop) & "_" & strSuffix

 

[Kevin]

Thanks guys, Is working fine now.