Microsoft Access VBA Programming for the Absolute Beginner

Copies a database object into the same or another database.

Syntax

DoCmd.CopyObject([DestinationDatabase][, NewName][, SourceObjectType][, SourceObjectName])

with the following parameters:

DestinationDatabase

The path and filename of the Access database to which the object is to be copied. If the current database, the argument should be omitted.

NewName

The name to be given the copy of the object. If it is to have the same name as SourceObjectName, this argument can be omitted.

SourceObjectType

An AcObjectType constant indicating the type of object to copy. Possible values are acDataAccessPage, acDefault (the object selected in the Database window), acDiagram, acForm, acFunction, acMacro, acModule (a VBA module), acQuery, acReport, acServerView, acStoredProcedure, and acTable.

SourceObjectName

The name of the object to be copied.

Example

Public Sub MakeBackupCopy() ' Make backup copy of Purchases table DoCmd.CopyObject , "tblPurchases Backup", acTable, "tblPurchases" ' Disable warnings DoCmd.SetWarnings False 'Run update query to multiple prices by 10% DoCmd.OpenQuery "UpdatePrices" ' Turn warnings back on DoCmd.SetWarnings True End Sub

Comments

• Although all arguments are optional, either the DestinationDatabase or NewName argument must be provided. This is because Access must make a copy of the object either in the same database (in which case it needs to name the copy something other than the original object) or in another database (where it can presumably use the object’s original name).

• The default action of the method (if SourceObjectName is not provided and SourceObjectType is acDefault or is not provided) is to copy the object selected in the Database window. This can be controlled programmatically with the DoCmd.SelectObject method.

• The CopyObject method is particularly useful before executing a query that may change multiple records in a table, such as an update query.

• If warnings are on (see the SetWarnings method) and the object indicated by NewName already exists (that is, if the method is attempting to assign the copy a name that has already been assigned to an object), Access will prompt whether to overwrite it. If warnings are off, Access will simply overwrite the object named NewName.