In an Access project (*.adp), you can use the TransferSQLDatabase macro action to transfer a Microsoft SQL Server 7.0 or later database to another SQL Server 7.0 or later database. For more information on transferring a database, see the SQL Server documentation.
Note: This action will not be allowed if the database is not trusted.
Note: Access 2010 is the last version that supports Access data projects.
Setting
The TransferSQLDatabase macro action has the following arguments.
|
Action argument |
Description |
|
Server |
The name of the SQL Server 7.0 or later database server you are copying to. |
|
Database |
The name of the new database that will be created on the destination server. |
|
Use Trusted Connection |
Specifes whether or not there is a trusted connection to the SQL Server. If set to Yes, then there is a trusted connection and the Login and Password arguments are not required. If set to No, the Login and Password arguments are required. The default is Yes. When you use a trusted connection, SQL Server security integrates with the Windows operating system security to provide a single log on to the network and the database. |
|
Login |
The name of the Login to the destination server. |
|
Password |
The password for the Login argument. This password is stored as text in the Access project, but is hidden during the transfer database operation. |
|
Transfer Copy Data |
Specifies whether or not to include data in the transfer database operation. When set to Yes, all data is included for all the tables, along with all data structures, extended properties, and database objects. When set to No, no data is included from the tables. Only the table structure and extended properties are created on the destination server, along with all other database objects (except database diagrams). The default is Yes. |
Remarks
You cannot perform other operations while the database is being transferred.
The TransferSQLDatabase macro action, by default, copies data, data definitions, database objects, and extended properties, such as default values, text constraints, and lookup values.
There are requirements for transferring a database:
-
You must be a member of the sysadmin role on the destination server (No special role is required on the source server).
-
The current SQL server connected to the Access project (*.adp) and the destination server you are transferring the database to must be SQL Server version 7.0 or later.
Note: Linked servers are not transferred during a database transfer operation.
To run the TransferSQLDatabase macro action in a Visual Basic for Applications (VBA) module, use the TransferSQLDatabase method of the DoCmd object.
