Access and Other Databases

You can use the TransferDatabase method of the DoCmd object to import from, link to, and export data to Access and several other databases, including SQL Server and Oracle. The basic syntax of the TransferDatabase method is shown in the following code:

  DoCmd.TransferDatabase TransferType, DatabaseType, DatabaseName, ObjectType, Source, Destination, StructureOnly, StoreLogin 

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
TransferType	Type of transfer to be performed. Valid choices are acImport (default), acLink, and acExport.
DatabaseType	Type of database being used. Access is the default. See the help documentation for a complete list and for the exact syntax for a particular database.
DatabaseName	The full name, including the path, of the database being used.
ObjectType	The type of object that has data you want to work with. The default is acTable.
Source	Name of the object whose data you want to work with.
Destination	Name of the object in the destination database.
StructureOnly	Use True to work with the structure only and False to work with the structure and data. False is the default.
StoreLogin	Whether to store the login and password. False is the default.

Let’s look at an example. Suppose that you want to import data from an Access database called SampleDb. The data you want to import is in a table called Sales, and you want it to be imported to your current database under the name tblSales. You could run the following command from your current Access application:

  DoCmd.TransferDatabase acImport, "Microsoft Access",_      "SampleDb.mdb", acTable, "Sales", "tblSales" 

Here’s an example that shows linking to a table called Sales in an ODBC database called Wrox:

  DoCmd.TransferDatabase acLink, "ODBC Database", _     "ODBC;DSN=DataSourceName;UID=username;PWD=pwd;            LANGUAGE=us_english;" _     & "DATABASE=Wrox", acTable, "Sales", "dboSales" 

The ODBC data source name can point to any database that ODBC supports, including SQL Server and Oracle, to name a few examples. As with any linking operation, you see the table or tables from the Database Window in Access.

Warning 

There is no Undo option for database transfers. Make sure that you have a current backup of both databases before you begin. Furthermore, be very sure to verify the correctness of the Source and Destination parameters.

Try It Out-Importing Data from the Sample Northwind Database

Now it’s your turn to try this out. Let’s import data from the sample Northwind database that comes with Access.

1. Insert a new module into your Ch7CodeExamples database.

2. Add the following code to the module:

     Sub TestTransferDatabase() 'import from Northwind DoCmd.TransferDatabase acImport, "Microsoft Access", _     "C:\Program Files\Microsoft Office\OFFICE12\SAMPLES\Northwind.accdb", __ acTable, "Employees", "tblEmployees" End Sub 

3. Modify the preceding path to the location on your hard drive where Northwind.mdb is located. If you do not have the sample Northwind database installed, change the previous parameters to reference the Access database that you do have.

4. From the Immediate Window in the Visual Basic Editor, type TestTransferDatabase and press Enter to run the procedure. You may receive a warning message similar to that in Figure 7-1, in which case you should click the Open button.

   
   Figure 7-1

5. Open the Database Window and you should see a screen similar to Figure 7-2.

   
   Figure 7-2

How It Works

In this example, you used the TransferDatabase method to import data from the Northwind sample database. The parameters of the TransferDatabase method specified the various bits of information Access needed to perform the import:

 Sub TestTransferDatabase() 'import from Northwind DoCmd.TransferDatabase acImport, "Microsoft Access", _     "C:\Program Files\Microsoft Office\OFFICE12\SAMPLES\Northwind.accdb", _             acTable, "Employees", "tblEmployees" End Sub

After you ran the procedure, you should have noticed in the Database Window that the new table was inserted into your database.