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.

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

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.

DoCmd.TransferDatabase acImport, "Microsoft Access", _

"C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb", _ 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.

5. Open the Database Window and you should see a screen similar to Figure 7.1.

j Ch7CodeExamples : Database (Access 2... - S]0

J Open /Design _|New X -_ ;iïï

Objects

SJ Create tabie in Design view ^J Create tabie by using wizard i^J Create tabie by entering data

J Tables

Queries =il Forms U Reports "d] Pages

...1 itblEmployeesi

Figure 7.1

0 0

Post a comment