ACC2000: Jet Table Constraint Silently Fails to Upsize

Article translations Article translations
Article ID: 201310 - View products that this article applies to.
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.

Expand all | Collapse all

On This Page

SYMPTOMS

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

RESOLUTION

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

STATUS

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

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.

REFERENCES

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

Properties

Article ID: 201310 - Last Review: October 11, 2013 - Revision: 2.1
APPLIES TO
  • Microsoft Access 2000 Standard Edition
Keywords: 
kbnosurvey kbarchive kbbug kbfix KB201310

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com