ACC2000: Jet Table Constraint Silently Fails to Upsize (201310)

MORE INFORMATION

Steps to Reproduce Problem

The following steps involve creating a new database containing a table with a CHECK constraint, upsizing it to SQL Server, creating a new Microsoft Access project in which to view the SQL Server database, and then, running a stored procedure that reveals that the constraint is not upsized:

1. Create a new database called JetConstraint in the root directory of drive C.
2. Create a table with a CHECK constraint.
3. On the Tools menu, point to Database Utilities, and then click Upsizing Wizard.
4. In the Upsizing Wizard, click Create New database, and then click Next.
5. On the next screen, type a valid SQL Server with the appropriate login id and password, and in the Database Name box, type the word "Test" (without the quotation marks).
6. Click Next and add the two columns, Customers and Credit Limit.
7. Click Finish to create the Test database.

When you see the Upsizing Wizard Report, do the following:

1. Close the Upsizing Wizard Report.
2. In Access, on the File menu, click New.
3. In the New dialog box, click Project (Existing Database), and then click OK.
4. Name the new Access project "CSTest," and click Create.
5. In the Connection dialog box, type the name of the Microsoft SQL Server to which you upsized the Test database. Type the appropriate login id and password, as well as the database name "Test."
6. Click OK to create the new CSTest project.

On the Tables tab, you see your table.

1. On the Stored Procedures tab, click New.
2. On a new line just after the word "AS", type the following Transact SQL statement:

   SELECT * FROM sysobjects WHERE xtype = 'C'

3. Save the new procedure as ListConstraints and close it.
4. Select the new ListConstraints stored procedure, and click Run.

Note that the results of the procedure lists no constraints. The constraint on the Customers table is not upsized.