ACC2000: Data Not Upsized in Table with Index (225993)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q225993
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

When you examine an upsized table that should contain records, you find it contains no data, even though no errors were reported in the Upsizing Report.

CAUSE

A field in your table contains a null value for more than one record and has the following attributes:
  • The Indexed property is set to Yes (No Duplicates).
  • The Required property is set to No.

RESOLUTION

If a field in your table is indexed using the No Duplicates option, do not add more than one record that contains a null value for the column in question.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Start Microsoft Access, and in the opening Microsoft Access dialog box, click Blank Access Database. Click OK.
  2. Name the database NullTest, and click Create.
  3. In the Database window, click Tables.
  4. Click New, click Design View, and then click OK.
  5. Create the following table:
    Field NameData TypePrimary Key?Indexed
    MyPKAutoNumberYesYes (No Duplicates)
    MyTestFieldTextNoYes (No Duplicates)
  6. Save the table as MyTable.
  7. Open the MyTable table in Datasheet view.
  8. Add the following three records to the empty table:
    MyPKMyTestField
    1x
    2y
    3x

    NOTE: Values in the MyPK field are automatically populated. Do not try to manually add this data.
  9. Select the value "y" in the second record of the table and delete it.

    NOTE: To delete the value, select to highlight the character and use the DELETE key to remove it. Do not press SPACEBAR.
  10. Delete the value "x" from the first record.
  11. Close the Table.
  12. On the Tools menu, point to Database Utilities, and then click Upsizing Wizard.
  13. Select the Create new database option when prompted, and click Next.
  14. On the next screen, provide a Server Name to connect to and the Login Id and Password of an account that has permissions on the server. Use the database name suggested by the wizard.
  15. Click Next, and then choose to export the MyTable table to SQL Server. Click Next twice.
  16. In the What application changes do you want to make group box, select Create a new Access client/server application.
  17. Click Finish, and allow the Upsizing Wizard to complete its work.
  18. View the Upsizing Report and note that no errors appear. After you have examined the report, close it.

    After a moment, the new Access project that was created by the wizard will automatically open.
  19. In the Database window, click Tables.
  20. Double-click the MyTable table and note that it contains no data.

REFERENCES

For more information about upsizing databases, click Microsoft Access Help on the Help menu, type upsize your Microsoft Access database in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MinorLast Reviewed:1/26/2005
Keywords:kbbug kbpending KB225993
©2004 Microsoft Corporation. All rights reserved.