ACC2000: MS Access Behavior When Importing Data to Fields with Default Values (207784)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q207784
This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

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

SYMPTOMS

When you import data into an existing Microsoft Access table that has default values specified for certain fields, the default values will not be used to populate any blank fields for the imported data.

CAUSE

Access does not make use of default values when importing data.

MORE INFORMATION

Access does not use the default value for a field to change existing data. It does not matter if the existing data exists in an Access table or in an external file; the individual fields will not be modified by the default value.

Steps to Reproduce Behavior

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

  1. Open the sample database Northwind.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 "Chicago" to the DefaultValue property of the City field.
  4. Close and save the table.
  5. Open the Customers table in Datasheet view and delete several of the cities from the City field, and then close the table.
  6. Follow these steps to export the table:
    1. On the File menu, click Export.
    2. In the Save in box, select the root of drive C. In the Save as type box, select Text Files, and then click Save.
    3. In the Export Text Wizard, select Delimited, and then click Next.
    4. Click to select the Include Field Names on First Row check box, select the Comma delimiter, select the single quotation mark (") as the Text Qualifier, and then click Finish.

      This will create the Customer.txt file in the C:\ folder.
    5. Click OK when informed that the export is complete.
  7. Follow these steps to import the table:
    1. On the File menu, point to Get External data, and then click Import.
    2. In the Import dialog box, select the root of drive C in the Look in box, select Text Files in the Files of type box, select the Customers.txt file, and then click Import.
    3. In the Import Text Wizard, select Delimited, and then click Next.
    4. Click to select the Include Field Names on First Row check box, select the Comma delimiter, select the single quotation mark (") as the Text Qualifier, and click Next.
    5. Select In an Existing Table, click CopyOfCustomers, and then click Finish.
    6. Click OK when informed that the import is complete.
  8. Open the CopyOfCustomers table in Datasheet view.

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

REFERENCES

For more information about importing data, click Microsoft Access Help on the Help menu, type Import data in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MinorLast Reviewed:7/15/2004
Keywords:kbprb kbusage KB207784
©2004 Microsoft Corporation. All rights reserved.