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

ID: Q207784

The information in this article applies to:
This article applies to a Microsoft Access database (.mdb) and 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: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform 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.

Additional query words: 


Keywords          : kbusage kbdta IsmTxtd 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: May 13, 1999