ACC2000: Upsizing Wizard Does Not Create Constraint for AllowZeroLength Property

View products that this article applies to.

This article was previously published under Q227211

Advanced: Requires expert coding, interoperability, and multiuser skills.

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

↑ Back to the top

Symptoms

When you upsize a table containing a field with the AllowZeroLength property set to No, the Microsoft Access 2000 Upsizing Wizard does not create a trigger or CHECK constraint in an attempt to enforce the rule.

↑ Back to the top

Resolution

After the Upsizing Wizard finishes, manually create a CHECK constraint or trigger to enforce the rule.

To Create a CHECK Constraint

1.	Complete the steps in the Steps to Reproduce Behavior section of this article to upsize the Employees table.
2.	Open the Access project that you created by following the instructions in the Steps to Reproduce Behavior section of this article.
3.	Open the Employees table in Design view.
4.	Right-click inside the Table Designer and click Properties.
5.	On the Tables tab, click New.
6.	In the Constraint expression box, type the following: ([LastName] <> '') NOTE: There are no spaces between the apostrophes ('') in the sample above.
7.	Close the Properties dialog box, and then Save the table.
8.	Open the Employees table in Datasheet view.
9.	Change Nancy Davolio's last name to "".
10.	Move off the record. Note that you receive an error message.

↑ Back to the top

More information

Steps to Reproduce Behavior

1.	Open the sample database Northwind.mdb.
2.	Open the Employees table in Design view, and examine the properties of the LastName field. Note that the AllowZeroLength property is set to No.
3.	Close the Employees table.
4.	On the Tools menu, point to Database Utilities, and then click Upsizing Wizard.
5.	On the first screen of the wizard, click Create new database, then click Next.
6.	Specify a Server Name, Login ID, Password, and Database Name in the boxes provided. Click Next.
7.	In the Available Tables list, click Employees, move it to the Export to SQL Server list, and then click Next.
8.	Accept the default options and click Next.
9.	Select the Create a new Access client/server application option. In the ADP File Name box, type C:\AccessCS.adp, and then click Finish. NOTE: You will encounter multiple errors during the upsizing process. This is expected. Click OK each time that you are prompted.
10.	When the upsizing process is finished, close the Upsizing Wizard Report. The Access project, AccessCS.adp, created by the wizard should start.
11.	In the newly created Access project, right-click the Employees table and click Triggers.
12.	Examine both the Employees_ITrig and Employees_UTrig triggers, and note that neither contains SQL prevent zero length strings from being used in the LastName column.
13.	Open Employees in Design view, right-click inside the Table Designer, and click Properties.
14.	On the Tables tab, note that no CHECK constraints have been created to prevent a zero length string from being used in the LastName column.

↑ Back to the top

References

For more information about constraints, see the SQL Server "SQL Server Architecture," in SQL Server Books Online. To download the SQL Server Books Online, please visit the following Microsoft Web site:

http://download.microsoft.com/download/SQL70/File/2/Win98/En-US/SQLBOL.exe

↑ Back to the top