Cascading update problem

[Beverly Butler]

I am brand new to this forum, and I hope my question is not too simplistic.

I have 3 tables that have a common field. When I update the main table, I want the common field to be updated in the other two tables. This worked for quite some time, just using the cascade update, cascade delete settings for referential integrity.

However, I can no longer get them to cascade. I don't know of anything I have done differently. I then tried to get them to update using an update query, and I get the message that the records cannot be added to the second table because of a key violation. The primary key for the main table is the field I am trying to get to cascade update. The primary key for the second table is an autonumber.

I'd appreciate any help anyone can give me.

 

[Pat Hartman]

Open the relationship window and make sure that the relationships are still properly set.

BTW, although Access supports cascade update of a primary key changes to all related foreign keys it is generally poor practice to use a primary key that changes frequently. You are better off using an autonumber and if the buseniss rules require an alternate unique index, make that your present primary key.

 

[Beverly Butler]

My relationships are one-to-many (main table to the other two) with referential integrity enforced and cascading updates and deletes. Is this correct?

 

[Beverly Butler]

BTW, although Access supports cascade update of a primary key changes to all related foreign keys it is generally poor practice to use a primary key that changes frequently. You are better off using an autonumber and if the buseniss rules require an alternate unique index, make that your present primary key. [/B][/QUOTE]

The primary field does not often change, but we do have additions and deletions of records. Occasionally there will be a change, but mostly I am just trying to make the additions to my main table by a data form and have the information pass to the other tables.

 

[Pat Hartman]

Update is a general term used to lump the add/change/delete operations. Cascade update will propagate key changes from the PRIMARY KEY of the parent table to the FOREIGN KEY of related records in the child table. Cascade delete will delete all rows in a child table that are releted by their foreign key to the row being deleted in the parent table. There is no such thing as a cascade add operation. How would the database engine know what data needed to be added to the child table?

So which of the update operations is not working for you?

 

[Beverly Butler]

When there is an addition (new record) to the main table--primary key--I expect it to also add this information to the related tables foreign key.

I know this is possible, because it was working for more than a year. Access created its own macro to sync the tables, called something like "SyncPeriodicals!Current Payments." This db was created in Access 97 and converted to Access 2000. Does that make a difference?

I can't find instructions in either my manual or in Access Help on sychronizing tables in Office 2000.

 

[Pat Hartman]

This has never been a possibility nor will it ever be. Access is not prescient If related rows were being added "automatically", it was because you had some code that was doing it. Access was not doing it of its own volition. Think about what you are saying. If you add a new Order to an orders table, how would Access be able to "automatically" add rows to an order details table.

If your form is based on a query that links two tables. Rows will be added to both tables if and only if, you have provided data for the required columns of BOTH tables. If you only provide data for the parent table, NO row will be added to the related child table.