Troubleshoot tables and field properties (MDB)

Microsoft Office Access 2003

Microsoft Access can't create a primary key for a field that already contains data if that data includes duplicate values or Null values. If you can't readily eliminate Null values or duplicate entries by editing your data, do one of the following:

  • To locate records with duplicate values, use a Find Duplicates query.
  • To locate records with Null values, create a select query, and type the expression Is Null in the Criteria cell for the field where you want to set the primary key.
  • Choose a different field for your primary key.
  • Add an AutoNumber field and set it as the primary key.
  • Define a multiple-field primary key.

ShowThe caption of a field in my table has been deleted.

If you rename a field with a caption while in Datasheet view, the caption is deleted and replaced with the new field name. To redefine the field's caption, open the table in Design view, click the field for which you want to define a different column name, and in the lower portion of the window, type the new column name in the Caption property box.

ShowWhen I compact my Microsoft Access database after changing the starting value of a AutoNumber field, the AutoNumber value for the new record is reset to 1.

If you want to compact the database after changing the starting AutoNumber value, make sure to add at least one record to the table first. If you don't, when you compact the database, the AutoNumber value for the next record added will be reset to 1 more than the highest previous value. For example, if there were no records in the table when you reset the starting value, compacting would set the AutoNumber value for the next record added to 1; for example, if there were records in the table when you reset the starting value and the highest previous value was 50, compacting would set the AutoNumber value for the next record added to 51.

ShowWhen I type a number in a field or control, only zeros appear to the right of the decimal point.

The FieldSize property of the field is Integer or Long Integer. Integers are whole numbers that contain no decimals or fractions. Even when the DecimalPlaces property of an integer field is set to a number greater than zero, only zeros appear to the right of the decimal point. Change the FieldSize property, or change the data type to Currency.