How to rename an Access Table using VBA from Excel?

Question

I´m trying to rename an Access Table using VBA from Excel... any help?

Answer 1

How about:

Dim appAccess As Object
''acTable=0

Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase "C:\Docs\LTD.mdb"

appAccess.DoCmd.Rename "NewTableName", 0, "OldTableName"

appAccess.Quit
Set appAccess = Nothing

Answer 2

Here's an example from one of my programs (which still is in daily use at the company). It's taken from a vb6 program, but also executes in vba. I've tested it to be sure.

In this example we have a temporary table with the name "mytable_tmp", which is updated with new data and we'd like to save this to the table "mytable" by replacing it.

From your Excel vba editor you'll need to set a reference to the following two type libraries:

• "Microsoft ActiveX Data Objects 2.8 Library"
• "Microsoft ADO Ext. 2.8 for DDL and Security"

The first one is for the ADODB namespace and the second for the ADOX namespace. (Maybe you have an earlier version of MDAC like 2.5 or earlier; this should work too).

Private Sub RenameTable()
Dim cn         As New ADODB.Connection
Dim cat        As ADOX.Catalog
Const sDBFile  As String = "c:\et\dbtest.mdb"

   On Error GoTo ErrH

   With cn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Mode = adModeShareDenyNone
      .Properties("User ID") = "admin"
      .Properties("Password") = ""
      .Open sDBFile
   End With

   Set cat = New ADOX.Catalog
   cat.ActiveConnection = cn
   cat.Tables("mytable").Name = "mytable_old"
   cat.Tables("mytable_tmp").Name = "mytable"
   cat.Tables("mytable_old").Name = "mytable_tmp"

ExitHere:
   If Not cn Is Nothing Then
      If Not cn.State = adStateClosed Then cn.Close
      Set cn = Nothing
   End If
   Set cat = Nothing
   Exit Sub

ErrH:
Dim sMsg As String
   sMsg = "Massive problem over here man."
   sMsg = sMsg & vbCrLf & "Description : " & cn.Errors.Item(0).Description
   MsgBox sMsg, vbExclamation
   GoTo ExitHere
End Sub

Hoping to be helpful.