How to rename an Access Table using VBA from Excel?

I´m trying to rename an Access Table using VBA from 开发者_JAVA技巧Excel... any help?

---

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.

---

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

---

Here is a slight alternative to Remou's code above. I use the shell function to open the database I need and then the GetObject function to access its properties and methods. The advantages to doing it this way are 1) You can select how the window for the Access application will open. For my purposes, I want it to be hidden. 2) I have both Access 2003 and 2007 installed and Remou's method causes 2003 to open, which I do not want. My method (I think) opens the file in whatever version of Access windows would have used to open it had the user double clicked on it.

The downside is that you must make sure the database is open before attempting to manipulate it. I use a simple wait subroutine to deal with this, but there are more sophisticated things you can do.

Sub Rename()
    Dim ObjAccess As Object, MDB_Address As String, TaskID As Integer

    MDB_Address = "C:\example.mdb"

    TaskID = Shell("msaccess.exe " & Chr(34) & MDB_Address & Chr(34), vbHide)
    Call Wait
    Set ObjAccess = GetObject(MDB_Address)
    ObjAccess.DoCmd.Rename "NewTableName", 0, "OldTableName"
    ObjAccess.Quit
    Set ObjAccess = Nothing

End Sub

Sub Wait()

    Dim nHour As Date, nMinute As Date, nSecond As Date, waitTime As Date

    nHour = Hour(Now())
    nMinute = Minute(Now())
    nSecond = Second(Now()) + 5
    waitTime = TimeSerial(nHour, nMinute, nSecond)
    Application.Wait waitTime

End Sub