ACC2000: Jet Table Constraint Silently Fails to Upsize

When you upsize a Microsoft Access database that has constraints, the constraints are not upsized.

After upsizing, re-create constraints in Microsoft SQL Server.

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

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.

For additional information about creating constraints in a Jet database, please see the following article in the Microsoft Knowledge Base: For additional information about using SQL DDL to create and drop tables and relationships, please see the following article in the Microsoft Knowledge Base: