Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources]

Importing External Data

The process of importing external data is quite simple. You can import external data by using the user interface or by using VBA code. If you are planning to import the data only once or twice, you should use the user interface. If you are importing data on a regular basis (for example, from a downloaded mainframe file), you should write code that accomplishes the task transparently to the user.

Using the User Interface

To import an external data file using the user interface, follow these steps:

Right-click anywhere within the database window.

Choose Import (or choose File, Get External Data, Import). The Import dialog box appears, as shown in Figure 19.1.

Figure 19.1. The Import dialog box.

From the Files of Type drop-down list, select the type of file you are importing.

Select the file you want to import and click Import.

Depending on the type of file you select, the import process finishes, or you see additional dialog boxes. If you select Excel Spreadsheet, for example, the Import Spreadsheet Wizard appears, as shown in Figure 19.2. This wizard walks you through the process of importing spreadsheet data.

Figure 19.2. The Import Spreadsheet Wizard.

CAUTION

If you find that you can't bring a large (4MB5MB) text file directly into an Access database, change the text file into an Excel spreadsheet first and then import that file.

Using Code

The DoCmd object has three methods that assist you with importing external data: TransferDatabase, TransferText, and TransferSpreadsheet, each of which I cover in this section.

Importing Database Data Using Code

You use the TransferDatabase method of the DoCmd object to import data from a database such as FoxPro, dBASE, Paradox, or another Access database. Listing 19.1, included in basImport, shows an example that uses the TransferDatabase method.

Listing 19.1 Using the TransferDatabase Method

Sub ImportDatabase() DoCmd.TransferDatabase _ TransferType:=acImport, _ DatabaseType:="dBASE III", _ DatabaseName:= CurrentProject.Path, _ ObjectType:=acTable, _ Source:="Customer", _ Destination:="tblCustomers", _ StructureOnly:=False End Sub

NOTE

This code and all the code in this chapter are located in the CHAP19EX.MDB file on the sample code CD-ROM.

Table 19.1 lists the arguments for the TransferDatabase method.

Table 19.1. TransferDatabase Arguments

Argument	Specifies
TransferType	Type of transfer being performed.
DatabaseType	Type of database being imported.
DatabaseName	Name of the database. If the table is a separate file (as is the case with dBASE, Paradox, and earlier versions of FoxPro), the database name is the name of the directory that contains the table file. Do not include a backslash after the name of the directory.
ObjectType	Type of object you want to import. This argument is ignored for all but Access objects.
Source	Name of the object you are importing. Do not include the file extension.
Destination	Name of the imported object.
StructureOnly	Whether you want the structure of the table only or the structure and data.
StoreLogin	Whether you want to save the login ID and password for an ODBC database in the connection string for linked tables.

Importing Text Data Using Code

You use the TransferText method of the DoCmd object to import text from a text file. Listing 19.2 shows an example of this method.

Listing 19.2 Using the TransferText Method

Sub ImportText() DoCmd.TransferText _ TransferType:=acImportDelim, _ TableName:="tblCustomerText", _ FileName:=CurrentProject.Path & "\Customer.Txt" End Sub

Table 19.2 lists the arguments for the TransferText method.

Table 19.2. TransferText Arguments

Argument	Specifies
TransferType	Type of transfer you want to make
SpecificationName	Name for the set of options that determines how the file is imported
TableName	Name of the Access table that will receive the imported data
FileName	Name of the text file to import from
HTMTableName	Name of the table or list in the HTML file that you want to import or link to
CodePage	A long integer used to indicate the character set of the code page
HasFieldHeadings	Whether the first row of the text file contains field headings

Importing Spreadsheet Data Using Code

You use the TransferSpreadsheet method of the DoCmd object to import data from a spreadsheet file. Listing 19.3 shows an example that uses the TransferSpreadsheet method.

Listing 19.3 Using the TransferSpreadsheet Method

Sub ImportSpreadsheet() DoCmd.TransferSpreadsheet _ TransferType:=acImport, _ SpreadsheetType:=acSpreadsheetTypeExcel9, _ TableName:="tblCustomerSpread", _ FileName:=CurrentProject.Path & "\Customer.Xls", _ HasFieldNames:=True End Sub

Table 19.3 lists the arguments for the TransferSpreadsheet method.

Table 19.3. TransferSpreadsheet Arguments

Argument	Specifies
TransferType	Type of transfer you want to make.
SpreadsheetType	Type of spreadsheet to import from. The default is Excel 3.0.
TableName	Name of the Access table that will receive the imported data.
FileName	Name of the spreadsheet file to import from.
HasFieldNames	Whether the first row of the spreadsheet contains field headings.
Range	Range of cells to import.