ACC97: MS Access Behavior When Importing Text Depends on Version

• Microsoft Access 97

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

When you import a delimited text file into an existing Microsoft Access table and that table has a default value set for a particular field, the following behavior will occur during the import:

In Microsoft Access 7.0 and 97

   For cells in the field that were blank before the import, the cells
   will remain blank.

In Microsoft Access 2.0

   For cells in the field that were blank before the import, the cells will
   be populated with the default value.

CAUSE

The behavior of Microsoft Access has changed in the versions later then Microsoft Access 2.0. Microsoft Access 7.0 and 97 will not add the default value of fields to blank cells when the data is imported.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

1. Open Microsoft Access 2.0 and the sample database Nwind.mdb.

2. Make a copy of the Customers table(Structure Only) and save the copy as CopyOfCustomers.

3. Open the CopyOfCustomers table in Design view and add the default value "Charlotte" (without the quotation marks) to the City field. Close and save the table.

4. Open the Customers table in Datasheet view and delete the City field value from several cells. Close and save the table.

5. Follow these steps to export the table:

       a. On the File menu, click Export.
   

       b. In the Data Destination box, click Text(Delimited), and then click
          OK.
   

       c. In the Object In NWIND box, select Customers, and then click OK.
   

       d. In the Export To File box, click OK.
   

       e. In the "Export Text Options - CUSTOMER.TXT" box, click the "Store
          Field Names in First Row" check box, and then click OK.
   

6. Follow these steps to import the table:

       a. On the File menu, click Import.
   

       b. In the Import box, click Text(Delimited), and then click OK.
   

       c. In the Select File box, select the Customer.txt file in the File
          Name box, and then click Import.
   

       d. In the "Import Text Options - CUSTOMER.TXT" box, click the "First
          Row Contains Field Names" check box, click "Append to Existing
          Table," and then select CopyOfCustomers in the list box.
   

       e. Click OK twice to the Import messages. Close the Select File box.
   

7. Open the CopyOfCustomers table.

   Note that for cells in the City field that were blank, the default value of Charlotte has been added.

8. Open Microsoft Access 7.0 or 97, and repeat steps 2 through 7 for the particular version of Microsoft Access.

   Note that for cells in the City field that were blank, the default value for the field has not been added.

REFERENCES