MS Access adding records

theduffer

Folks,
I need a bit of help and apologies if this is in the wrong location. I have two tables that have a 1:1 relationship. Is there a way of updating the two primary key fields simultainously? I keep getting the error "you cannot add or change arecord because a related record is required in FIRtable" ? If this isnt possible in access can anyone recommend a DBMS that allows this.

Any help is appreciated,

Torak

theduffer wrote:

Folks,
I need a bit of help and apologies if this is in the wrong location. I have two tables that have a 1:1 relationship. Is there a way of updating the two primary key fields simultainously? I keep getting the error "you cannot add or change arecord because a related record is required in FIRtable" ? If this isnt possible in access can anyone recommend a DBMS that allows this.

Any help is appreciated,

The most obvious question is why do you need to update the primary key fields?

I'm not sure about access but in any other dbms this is an extremely expensive operation as there will probably be a clustered index on the field. you really don't want that being re-arranged unless it has to be.. Access probably doesn't have this notion, but tis still an odd thing to do..

Any chance of a simple schema and a view of the update statements?

Seth Brundle

What datatype is your PK? If one table uses the autonumber then you can't edit it at all.
If neither table is using this then you need to change each while the corresponding key is still there. Were you to change the values in table1, table2 has no corresponding key and therefore the transaction fails.
What you could do is create a copy of the record in table1 and then using the new records PK copy the corresponding record in tbale2. then delete the original two records.
But again as Gosh says - why on earth would you do this? It sounds like our table structures have not been planned out properly.

theduffer

Torak wrote:

The most obvious question is why do you need to update the primary key fields?

I'm not sure about access but in any other dbms this is an extremely expensive operation as there will probably be a clustered index on the field. you really don't want that being re-arranged unless it has to be.. Access probably doesn't have this notion, but tis still an odd thing to do..

Any chance of a simple schema and a view of the update statements?

cheers for the reply torak. Im simply trying to add a record to both tables at the same time. both primary fields will require the same entry for data integrety purposes. the problem occours when 1 value is inputted for the primary key field is added, the other table's primary key doesnt have that same value. Is there a way of solving this problem?

theduffer

kbannon wrote:

But again as Gosh says - why on earth would you do this? It sounds like our table structures have not been planned out properly.

The reason I have done this is to keep data in seperate tables so certain data (other than the pk field) can be edited.I understand the way it's set up is messy and one table could be used if all else fails.thanks for the suggestion.

smythmark

theduffer wrote:

The reason I have done this is to keep data in seperate tables so certain data (other than the pk field) can be edited.I understand the way it's set up is messy and one table could be used if all else fails.thanks for the suggestion.

Is there any particular reason that the primary keys need to have the same value in each table? Wouldn't it be easier to let the pk value differ in each table, then alter one of the tables to store the pk of the related record (SQL hazy, Think this is a foreign key?) in a field. So you create the new record in one table, find the newly created value of the primary key for this record, put this in the foreign key field in the second table. Hope this helps somewhat

Adam

Smythmark is on the ball there, you want to do something like this with your insert/update statement:

[PHP]$query = "INSERT INTO table1 (field1, field2) VALUES ($value1, $value2)";
$result = mysql_query( $query );
$last_id = mysql_insert_id ();[/PHP]

That retrieves the id of the entry just inserted into table one and then you simply use $last_id as such:

[PHP]$query = "INSERT INTO table2 (field1, field2) ".
"VALUES ('$last_id', $other_values')";
mysql_query($query) or die('Error, query failed');[/PHP]

Torak

theduffer wrote:

The reason I have done this is to keep data in seperate tables so certain data (other than the pk field) can be edited.I understand the way it's set up is messy and one table could be used if all else fails.thanks for the suggestion.

I'm not being smart but do you know what normalisation is and why you should use it.

Relational databases only work if you design them properly.

As i said some schema and sql would help
You never answered my original question. Why do you need to UPDATE the primary key field?
Can you not leave it out of the SET clause?

then you will not hit the issue as the PK field will not be changing!

Show SQL?

democrates

Mark Torak well.

You may know this already, but for the benefit of learners:

The design goal of the primary key is to describe one specific thing, and for all time. The only time a Pk should change is recovering from errors or during certain system redesigns, but in normal use their whole job is to never change.

Eg, say car owners on an owner table have a PK of pps_number. One pps_number per person and that is them for life and no-one else. That's called entity integrity and applies to a single table.

Now say a car table has a PK of registration_number, same thing, one reg per car and that's it until the scrapheap (play along). But the car table may also have a column with a foreign key pps_number, which is the primary key of the owner table, to indicate who owns the car.

(This design choice only allows a car to have one owner, but a person could own many cars because their pps_number could be on any number of different car records on the car table)

If a car is sold, a new owner may have to be added to the owner table if they're not there already, but the car record on the car table only needs its pps_number updated, to point to the new owner.

If you add a car record with a pps_number that does not exist on the owner table, now you've lost relational integrity, and users of the system won't know who owns that car. MS Access like many RDBMS's can block this from happening and instead return an error message like the one you've described.

But note that there is no call for either primary key to get updated here. Add no problem, but not update.

If you give an example of your tables and why the need to change the primary keys I'm sure you'll get good advice on solving the problem.