Importing Data and Databases

You can copy data from a number of different file formats to create an Access table. In addition to copying data from a number of popular database file formats, Access 2010 can also create a table from data in a spreadsheet or a text file. When you copy data from another database, Access uses information stored by the source database system to convert or name objects in the target Access table. You can import data not only from other Access databases but also from dBASE and-using ODBC-any SQL database that supports the ODBC standard.

In Access 2010, Microsoft has deprecated support for importing or linking to Paradox and Lotus files.

Importing dBASE Files

On the companion CD, you'll find a dBASE 5 file named COMPANIE.dbf that you can use to follow along with the next procedure to import this file into the Conrad Systems Contacts sample database or into a new blank database. To import a dBASE file, do the following:

1. Open the Access database that will receive the dBASE file. If that database is already open, close all open objects so that you see only the Navigation pane.
2. On the External Data tab, in the Import & Link group, click the More command and then click dBASE File.
3. Access opens the Get External Data - dBASE File dialog box. Click Browse to browse for the dBASE file you need to import.
4. Access opens the File Open dialog box, shown next. Select dBASE III, dBASE IV, or dBASE 5, as appropriate, in the list to the right of the File Name box. (In Windows XP, this list is labeled Files Of Type and appears below the File Name box.) Select the source file folder, and then select or type the file name in the File Name box. If you're having difficulty finding the file you want, type a search string in the Search field.
5. Click the Open button to return to the Get External Data - dBASE File dialog box with the file path to the dBASE file you need in the File Name box. Make sure the first option, Import The Source Data Into A New Table In The Current Database, is selected, and then click OK to import the dBASE file you selected. Access displays a message that informs you of the result of the import procedure.
   If the import procedure is successful, the new table will have the name of the dBASE file (without the file name extension). If Access finds a duplicate table name, it will generate a new name by adding a unique integer to the end of the name. For example, if you import a file named Company.dbf and you already have tables named Company and Company1, Access creates a table named Company2.
6. Click Close to dismiss the message that confirms the import procedure.

When you look at a table imported from dBASE in Design view, you'll find that Access has converted the data types, as shown in Table-1.

DBASE Data Type 	Converts To Access Data Type
Character 		Text
Numeric 		Number, Field Size property set to Double
Float 			Number, Field Size property set to Double
Logical 		Yes/No
Date 			Date/Time
Memo 			Memo

As we noted earlier, we created the COMPANIE dBASE file from the Companies table you can find in the ImportLink sample database. You can open these two tables side by side to see the differences. First, dBASE doesn't support field names longer than 10 characters. So CompanyName in the original file is shortened to COMPANYNAM, and LastOrderDate appears as LASTORDERD. Also, dBASE doesn't support the Hyperlink, Currency, or Decimal data type, so it stores Hyperlink data types as Memo, and Currency and Decimal data types as Number, Double.