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 “DataTableNLR981911” 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(ByVal oldName As String, ByVal newName As String)
Dim cn As New ADODB.Connection
Dim catalog As New ADOX.Catalog
Dim i As Integer

cn.ConnectionString = mConnectionString
cn.Open()

catalog.ActiveConnection = 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'?