Microsoft Access VBA Programming for the Absolute Beginner

Renames a database object.

Syntax

DoCmd.Rename NewName[, ObjectType][, OldName]

with the following parameters:

NewName

A String defining the new name of the object.

ObjectType

An AcObjectType constant indicating the type of object to be renamed. Possible values are acDataAccessPage, acDefault (the object selected in the Database window, the default value), acDiagram, acForm, acFunction (a SQL Server user-defined function), acMacro, acModule, and acQuery, acReport, acServerView, acStoredProcedure, and acTable.

OldName

A String containing the old name of the object of type ObjectType. If omitted, Access renames the selected object.

Example

This sample iterates the AllTables collection looking for a table whose name includes the substring “Backup”. It asks whether the user wishes to restore the original table from the backup and if the user agrees, renames the backup file, thereby overwriting the original file.

Public Sub RestoreFromBackup() Dim tbl As Variant Dim intPos As Integer Dim strNewName As String For Each tbl In CurrentData.AllTables intPos = InStr(1, tbl.Name, " Backup") If intPos > 0 Then strNewName = Left(tbl.Name, intPos - 1) If MsgBox("Replace " & strNewName & " with " & tbl.Name _ & "?", vbYesNoCancel Or vbQuestion, _ "Rename Table") = vbYes Then DoCmd.SetWarnings False DoCmd.Rename strNewName, acTable, tbl.Name DoCmd.SetWarnings True End If End If Next End Sub

Comments

• To rename the object selected in the Database window, you can call the SelectObject method with its InDatabaseWindow argument set to True before calling the Rename method.

• An open object cannot be renamed.

• If warnings are set on (see the SetWarnings method) and NewName already exists, Access prompts the user whether he or she wants to replace it.