CODEWITHSUNDEEP
database-designms-access-2007relationshipcascade
mfg
mfg

Reputation: 185

Linked Tables not Updating in Access 2007

I am running into an issue with a pair of linked tables in Access 2007. My intent is to create a new record on the one and have it generate a new one on the other. Perhaps I am over-estimating the ability of relationships in Access, but I have them set up as follows:

Jeez, sorry - that compression looks awful...

So the relationship between the tables 'Contracts' and 'States' is one-to-one, set to enforce referential integrity, cascade update related fields, and only include where equal (I have also tried to use an include only from [Contracts] to [States]).

I have a form for the user to enter Contracts.ContractID (and other info), but the States.ContractID isn't updating (the form control does update Contracts.ContractID), or rather there isn't a new record being created on the table.

I anticipate that I may be trying to accomplish this completely incorrectly. Assistance that would help me form a better table layout would be preferred, but a better method of input would help as well.

(If there is an additional consideration with how these tables are keyed please let me know as the ContractID is an inherited key from another resource.)

Upvotes: 0

Views: 2343

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95761

I anticipate that I may be trying to accomplish this completely incorrectly.

You're right. Relationships don't work the way you think they do. In short, there's no "automatically insert a row into a different table when I insert a row into this table" feature in any SQL dbms. In some cases, you can program such a feature yourself.

In Access, your best bet is to use a form and a linked subform. This approach will automatically insert the key (linked) columns, and the user will fill in the rest of the data.

Upvotes: 1

Related Questions