ACC2000: Jet Table Constraint Silently Fails to Upsize

ID: Q201310

The information in this article applies to:
Advanced: Requires expert coding, interoperability, and multiuser skills.


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 this to be a problem in the Microsoft products 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:


    3. 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:

Q201888 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:
Q209037 ACC2000: Create and Drop Tables and Relationships Using SQL DDL

Additional query words: pra 


Keywords          : kbdta 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbbug

Last Reviewed: May 13, 1999