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:
Microsoft Access is a fully capable database and it's primary limitations is the number of concurrent users it will support. Generally, we recommend a limit of 10 to 15 users.
We create databases large and small. Some of our databases help run entire small businesses. We also handle unique businesses such as flyrod manufacturing, cheese making, marina management, and cable tv inventory.
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:
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
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
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", _
& "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
DoCmd.TransferDatabase transfertype:=acExport, databasetype:="WSS", _
objecttype:=acTable, Source:="Customers", _
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
The above command copied the Structure Only of the query called Q_Books to a new
query called q_test in my current database.