ACC2000: Jet Table Constraint Silently Fails to Upsize

This article was previously published under Q201310
This article has been archived. It is offered "as is" and will no longer be updated.
Advanced: Requires expert coding, interoperability, and multiuser skills.

When you upsize a Microsoft Access database that has constraints, theconstraints 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 atable with a CHECK constraint, upsizing it to SQL Server, creating a newMicrosoft Access project in which to view the SQL Server database,and then, running a stored procedure that reveals that the constraint isnot upsized:

  1. Create a new database called JetConstraint inthe 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 appropriatelogin 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:
201888 ACC2000: How to Create a Jet CHECK Constraint
For additional information about using SQL DDL to create and drop tables and relationships, please see the following article in the Microsoft Knowledge Base:
209037 ACC2000: Create and Drop Tables and Relationships Using SQL DDL

Article ID: 201310 - Last Review: 10/11/2013 20:43:58 - Revision: 2.1

Microsoft Access 2000 Standard Edition

  • kbnosurvey kbarchive kbbug kbfix KB201310
ERROR: at System.Diagnostics.Process.Kill() at Microsoft.Support.SEOInfrastructureService.PhantomJS.PhantomJSRunner.WaitForExit(Process process, Int32 waitTime, StringBuilder dataBuilder, Boolean isTotalProcessTimeout)