By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,183 Members | 1,386 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,183 IT Pros & Developers. It's quick & easy.

Change Access Table Name from Excel VBA

P: 8
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.
Mar 4 '08 #1
Share this Question
Share on Google+
2 Replies

P: 9
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

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


cn = Nothing
catalog = Nothing
End Sub
Mar 6 '08 #2

P: 8
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'?
Mar 6 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.