ACC2000: Data Not Upsized in Table with Index

View products that this article applies to.

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).

↑ Back to the top

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.

↑ Back to the top

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.

↑ Back to the top

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.

↑ Back to the top

Status

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

↑ Back to the top

More information

Steps to Reproduce Behavior

Start Microsoft Access, and in the opening Microsoft Access dialog box, click Blank Access Database. Click OK.

Name the database NullTest, and click Create.

In the Database window, click Tables.

Click New, click Design View, and then click OK.

Create the following table:

Field Name	Data Type	Primary Key?	Indexed
MyPK	AutoNumber	Yes	Yes (No Duplicates)
MyTestField	Text	No	Yes (No Duplicates)

Save the table as MyTable.

Open the MyTable table in Datasheet view.

Add the following three records to the empty table:

MyPK	MyTestField
1	x
2	y
3	x

NOTE: Values in the MyPK field are automatically populated. Do not try to manually add this data.

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.

↑ Back to the top

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.

↑ Back to the top