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

TransferTypeType of transfer to be performed. Valid choices are acImport (default), acLink, and acExport.
DatabaseTypeType 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.
DatabaseNameThe full name, including the path, of the database being used.
ObjectTypeThe type of object that has data you want to work with. The default is acTable.
SourceName of the object whose data you want to work with.
DestinationName of the object in the destination database.
StructureOnlyUse True to work with the structure only and False to work with the structure and data. False is the default.
StoreLoginWhether 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 ...

Get Beginning Access™ 2007 VBA now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.