Question Is my database corrupting itself?

[PTRACER]

Every so often, I need to restore a backup of my database, because I'll add a customer, add the details of their computer, create a new Repair (I have a computer repairs business that uses a database to log all repairs) and it will suddenly say to me:

You cannot add or change a record, because a related record is required in 'Computer'

If I add a new repair for existing customers, this doesn't happen.

Is my database corrupting itself, or what? I'm getting fed up with this.

 

[RuralGuy]

Your db is trying to tell you that your "Computer" table needs an entry before you try and enter a repair. This is called Referential Integrity and in this case is trying to keep you from repairing a computer that you have not defined yet. At least this is my best guess. It is *not* corruption.

 

[PTRACER]

I found the problem but I don't understand the cause.

The one-to-many relationship between tblComputer.ComputerID and tblRepair.ComputerID keeps changing to tblComputer.ComputerID and tblRepair.CustomerID.

I don't understand why it's doing that.

 

[RuralGuy]

What version of Access are you using and what are the Field Types of each of the fields involved?

 

[boblarson]

Check the relationships window and make sure that you didn't link to the wrong field accidentally.

 

[PTRACER]

I'm using Access 2003.

tblComputer.ComputerID = AutoNumber
tblRepair.ComputerID = Number

I've just gone through all my backups and it seems to have been set to ComputerID ------------ CustomerID all this time. I wonder how the damn thing ever worked! Sorry to have troubled you all, I'll keep a close eye on it...

 

[georgedwilkinson]

Are you SURE you want cascade update/delete? Delete makes a little sense (thought a bit dangerous), but you're going to cascade update an artificial key?

 

[RuralGuy]

Hopefully tblRepair.ComputerID = LONG

 

[PTRACER]

Are you SURE you want cascade update/delete? Delete makes a little sense (thought a bit dangerous), but you're going to cascade update an artificial key?

To be honest, I don't COMPLETELY understand what a cascade update will do. I don't want that box checked, no?

 

[RuralGuy]

To be honest, I don't COMPLETELY understand what a cascade update will do. I don't want that box checked, no?

Basically it allows you to *change* the key. Everywhere you have used the key as a ForeignKey will be changed to the new value.