我正在尝试使用Excel中的VBA重命名Access表格...有什么帮助吗?
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
希望能为您提供帮助。
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
这里是对Remou上面代码的轻微改进。我使用shell函数打开所需的数据库,然后使用GetObject函数访问其属性和方法。这样做的优点是: 1)您可以选择如何打开Access应用程序的窗口。对于我的目的,我希望它是隐藏的。 2)我安装了Access 2003和2007,Remou的方法会导致2003打开,而我不想要。我的方法(我认为)会在Windows中打开文件,无论用户是否双击它。
缺点是在尝试操作它之前必须确保数据库已打开。我使用一个简单的等待子例程来处理这个问题,但您可以做更复杂的事情。
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