TransferSQLDatabase Action

In a Microsoft Access project, you can use the TransferSQLDatabase 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.

Setting

The TransferSQLDatabase 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 the Login and Password arguments are required. The default is Yes. When you use a trusted connection, SQL Server security integrates with the Windows 2000 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

The transfer operation is synchronous, so you cannot perform other operations until the transfer of the database is complete.

The TransferSQLDatabase 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 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 action in Visual Basic, use the TransferSQLDatabase method of the DoCmd object.