如何使用Excel中的VBA重命名访问表？

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

我正在尝试使用Excel中的VBA重命名访问表...任何帮助？

3 个解决方案

#1

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.

这是我的一个程序的例子（它仍然在公司的日常使用中）。它来自vb6程序，但也在vba中执行。我已经测试过它了。

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.

在这个例子中，我们有一个名为“mytable_tmp”的临时表，它使用新数据进行更新，我们希望通过替换它将其保存到表“mytable”中。

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

在Excel vba编辑器中，您需要设置对以下两个类型库的引用：

"Microsoft ActiveX Data Objects 2.8 Library"
“Microsoft ActiveX数据对象2.8库”
"Microsoft ADO Ext. 2.8 for DDL and Security"
“用于DDL和安全的Microsoft ADO Ext.2.8”

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).

第一个用于ADODB命名空间，第二个用于ADOX命名空间。（也许你有早期版本的MDAC，如2.5或更早版本;这也应该有用）。

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.

希望能有所帮助。

#2

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

#3

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.

以上是Remou代码的略微替代方案。我使用shell函数打开我需要的数据库，然后使用GetObject函数来访问它的属性和方法。这样做的好处是1）您可以选择打开Access应用程序窗口的方式。为了我的目的，我希望它被隐藏起来。 2）我安装了Access 2003和2007，Remou的方法导致2003打开，我不想要。我的方法（我认为）在用户双击它的任何版本的Access窗口中打开文件。

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

#1