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