O'Reilly logo

Beginning Access™ 2007 VBA by Denise Gosnell

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

7.3. 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.

ParameterDescription
ServerName of the SQL Server.
DatabaseName of new database on specified SQL Server.
UseTrustedConnectionTrue if account has Administrator privileges, False otherwise and must specify Login and Password.
LoginLogin name. Ignored if UseTrustedConnection is True.
PasswordLogin password. Ignored if UseTrustedConnection is True.
TransferCopyDataUse 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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required