Docmd TransferDatabase Method Example

The TransferDatabase method is used to import or export data between the current Microsoft Access database or project and another database. Another key feature of the transferdatabase action is to link to tables in another database - this can be done programmatically and dynamically.

Below is the syntax of the Access transfer database method and a description of each of the arguments:

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

Transfer Type The type of transfer you want to make. There are three options for TransferType:

We also offer Microsoft SQL Server / Azure / Cloud database development through our programmer partnership with Jay McCormick and his team.

• acExport

• acImport (default setting)

• acLink (not supported for .adp databases)

Database Type The type of database to import from, export to, or link to. You can select Microsoft Access or one of the following other database types:

• Microsoft Access (default)

• dBase III

• dBase IV

• dBase 5.0

• Paradox 3.x

• Paradox 4.x

• Paradox 5.x

• Paradox 7.x

• ODBC Databases

• WSS

For databases such as FoxPro, Paradox and dBase that have separate files for each table, you will need to enter the directory and filename of the table in the Source argument in order to import or link to.

For ODBC type databases you'll need to enter the full Open Database Connectivity connection string.  See our ODBC connection method for string examples.  To see the proper connection string you can use the Get External Data wizard to link to a table and you will see the connection string Access establishes for the table.


Object Type The type of object to import or export. If you select Microsoft Access for the Database Type argument, you can select any one of the following object types:

acDataAccessPage
acDefault
acDiagram
acForm
acFunction
acMacro
acModule
acQuery
acReport
acServerView
acStoredProcedure
acTable default
 

Note on exporting Queries:  If you  export a select query to an Access database, select Table to export the result data set of the query, and select Query to export the query definition. Only the result data set is exported to other database types.

Source The name of the table, query, or other Access object to import, export, or link.  Note that the file extension is required when referencing external objects in databases other than MS Access.

Destination (required) The name of the imported, exported, or linked table, select query, or Access object in the destination database.

If you select Import in the Transfer Type argument and Table in the Object Type argument, Access creates a new table containing the data in the imported table.

If you import a table or other object, Access adds a number to the name if it conflicts with an existing name. For example, if you import Employees and Employees already exists, Access renames the imported table or other object Employees1.

If you export to an Access database or another database, Access automatically replaces any existing table or other object that has the same name.

Structure Only Specifies whether to import or export only the structure of a database table without any of its data. Select Yes or No. The default is No.
 

Here are a few specific examples of the transferdatabase method:

Import Master Contacts Report:

DoCmd.TransferDatabase acImport, "Microsoft Access", _
"C:\My Documents\Contacts.mdb", acReport, "Contact_List", "Master List of Contacts"

The next example links the ODBC database table Members to the current database:

DoCmd.TransferDatabase acLink, "ODBC Database", _
"ODBC;DSN=MyDataSource;UID=MyUserID;PWD=MyPassword;LANGUAGE=us_english;" _
& "DATABASE=pubs", acTable, "Members", "Members_Linked"

This example exports the structure and data of the Customers table to a new table Customers_All on the "http://demo/SP_Site" Windows SharePoint Services site.

DoCmd.TransferDatabase transfertype:=acExport, databasetype:="WSS", _
databasename:="http://example/SP_Site", _
objecttype:=acTable, Source:="Customers", _
Destination:="Customers_All", structureonly:=False

Here is a simple example from within visual basic I just tested:

Private Sub Command80_Click()

DoCmd.TransferDatabase acImport, "Microsoft Access", "D:\temp\my_data.mdb", acQuery, "Q_Books", "q_test", True

End Sub

The above command copied the Structure Only of the query called Q_Books to a new query called q_test in my current database.