Change Access Table Name from Excel VBA

[wbw]

How can I rename an Access table from Excel VBA? I know <DoCmd.Rename "new_name", acTable, "old_name"> however the ending characters of the "old_name" will vary from database to database. I need to know how to change “DataTableNLR98 1911” to just “DataTable”- the ending variable “NLR981911” will change and may vary in length too. Find the table 9 characters in length beginning with DataTable and then change it to DataTable. Thanks for your help.

[Wolfling]

[CODE]
Sub RenameTable(ByV al oldName As String, ByVal newName As String)
Dim cn As New ADODB.Connectio n
Dim catalog As New ADOX.Catalog
Dim i As Integer

cn.ConnectionSt ring = mConnectionStri ng
cn.Open()

catalog.ActiveC onnection = cn

For i = 0 To catalog.Tables. Count() - 1
If catalog.Tables( i).Name = oldName Then
catalog.Tables( i).Name = newName
Exit For
End If
Next


cn.Close()


cn = Nothing
catalog = Nothing
End Sub

[wbw]

I understand your code changes the table name from oldName to newName, but where does it define Table oldName to be the right 9 characters beginning with the word 'DataTable' and change newName to just 'DataTable'?