Transferring Complete SQL Server Database

The TransferSQLDatabase method allows you to transfer an entire SQL Server database to another database. In effect, this method imports the entire SQL Server database into your Access database. Here is the basic syntax:

  DoCmd.TransferSQLDatabase Server, Database, UseTrustedConnection, Login, Password, TransferCopyData 

Various parameters are used to specify how the method should execute. The following table explains the use of each parameter.

Open table as spreadsheet

Parameter	Description
Server	Name of the SQL Server.
Database	Name of new database on specified SQL Server.
UseTrustedConnection	True if account has Administrator privileges, False otherwise and must specify Login and Password.
Login	Login name. Ignored if UseTrustedConnection is True.
Password	Login password. Ignored if UseTrustedConnection is True.
TransferCopyData	Use True to work with the data and schema and False to work with the schema only.

For example, to transfer the entire contents of a database called Pubs to the current database, you can use a command similar to the following:

 DoCmd.TransferSQLDatabase _     Server:="ServerName", _     Database:="Pubs", _     UseTrustedConnection:=True, _     TransferCopyData:=False