Microsoft KB Archive/128877

= UT1x: Upsizing Wizard Does Not Allow Nulls in Foreign Key =

Article ID: 128877

Article Last Modified on 7/24/1999

-

APPLIES TO


 * Microsoft Access Upsizing Tools 1.0

-



This article was previously published under Q128877





SYMPTOMS
If after you have upsized Microsoft Access tables to Microsoft SQL Server, you attempt to save a record in which you have entered a null in a foreign key field, you may receive the following error messages

ODBC insert failed on attached table ''

and then:

Cannot add or change record. Referential integrity rules require a related record in table ''



CAUSE
The Upsizing Wizard creates an insert trigger when two tables with referential integrity are upsized. Entering a null in a foreign key field causes an error with this trigger. This error also occurs when you insert a record using the Microsoft SQL Server tools, such as ISQL/w.



RESOLUTION
To work around this problem, you must change the insert trigger to allow null values in the foreign key field. Please see the "Steps to Reproduce Problem" section of this article for an example of this resolution.

NOTE: This resolution assumes that you are familiar with Transact-SQL, the programming language used in Microsoft SQL Server. For more information about Transact-SQL, please refer to the "Transact-SQL Reference" manual for Microsoft SQL Server.



STATUS
Microsoft has confirmed this to be a problem in the Microsoft Access Upsizing Tools version 1.0. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.



Steps to Reproduce Problem

 * 1) Open the sample database NWIND.MDB.
 * 2) Upsize the Categories table and the Products table, using all the default settings. The Categories table is the parent table, and the Products table is the child table; Category ID is the foreign key.
 * 3) Attempt to enter a record in the newly attached Products table (Products_remote). Do not enter a value in the Category ID field. Note that you receive the error messages described earlier in this article.

To change the trigger to allow null values, change the original trigger created by the Upsizing Wizard that reads as follows. ' Prevent inserts if no matching key in 'Categories'.

IF (SELECT COUNT(*) FROM inserted) != (SELECT COUNT(*) FROM Categories, inserted WHERE _    (Categories.Category_ID = inserted.Category_ID)) BEGIN RAISERROR 44447 "Cannot add or change record._         Referential integrity rules require a related record in_          table 'Categories'." ROLLBACK TRANSACTION END to read: ' Prevent inserts if no matching key in 'Categories'.

IF (SELECT Category_ID FROM inserted) IS NOT NULL IF (SELECT COUNT(*) FROM inserted) != (SELECT COUNT(*) FROM Categories, inserted WHERE _    (Categories.Category_ID = inserted.Category_ID)) BEGIN RAISERROR 44447 "Cannot add or change record._        Referential integrity rules require a related record in_         table 'Categories'." ROLLBACK TRANSACTION END NOTE: In the sample code above, an underscore (_) is used as a line- continuation character. Remove the underscore from the end of the line when re-creating this code.

Keywords: kberrmsg kbbug KB128877

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.