Primary Key no longer Primary Key!

[Webskater]

I have created a very simple Access database with a CompaniesTbl, ContactsTbl and CallsTbl. The database is used to record telesales contact with customers. The tables are set up like this:

The CompaniesTbl has an AutoNumber Primary Key field called CompanyID.

The ContactsTbl has an AutoNumber Primary Key field called ContactID and a foreign key called CompanyID.

The CallsTbl has an AutoNumber Primary Key field called CallID and foreign key called ContactID.

The relationships are set up in the relationships window and referential integrity imposed.

Twice now, while editing a contact record, an error has occured. Task Manager has had to be used to get out of it. The error is either a 'run-time error with a message that does not mean anything' or it says 'unrecognized database format' (!?).

If you open the database again the ContactID field in the Contacts Table is no longer a Primary Key field. If you look in the relationships window - the relationship between the Companies and Contacts tables no longer exists.

Any ideas much appreciated.

 

[MStef]

Try to make "COMPACT AND REPAIR DATABASE".
Tools, Database utilities, Compact and repair database.

 

[The_Doc_Man]

Also, if you are the administrator of the machine, look in your control panel for the administrative functions, then open event viewer to see recent alerts for your applications and system logs. You should clearly see a time at which the Access problem occurs if you shut it down through Task Viewer. Look for any other system, security, or application events just before that time. You are looking for another error that might give you a more specific type of information as to what happened.

One very significant possibility is that if the database is on a file server, check with your network people to see if you have been having network dropouts at about that time. Since Access shared databases must use network services to do anything, a brief network failure would be flat out fatal and might exhibit the symptoms you describe.

 

[Webskater]

Thanks for your replies. I have repaired and compacted the database each time - to no effect. The database is not on a server. It only has one user so is just being used on a local machine. I haven't used Access for years - used to use it quite a bit - never seen anything like this before. I keep asking myself 'how can a field that is a Primary Key suddenly become a field that is not a Primary key' and 'why does the relationship between the tables get removed?'

 

[The_Doc_Man]

look in the event viewer for disk errors or application errors.

 

[Webskater]

Okay, I'm getting closer to this, but don't like what I am finding!

I have a form (which I'll call MainForm) which shows some of the details of a company, name, phone, status etc. If you want to edit the full details of the company, you click on the company name (hyperlink) and it opens a new form (which I'll call PopupEditForm) where all the details are displayed and you can edit.

If you change the phone number on the MainForm and then click the company name to open the PopupEditForm the phone number on the PopupEditForm has not changed - presumably because the change to the phone number on the MainForm has not been saved yet.

Now comes the scary part. If you change the phone number again on the PopupEditForm and close the PopupEditForm (in the Unload event of which I have MainForm.Requery) you get the message that says 'Another user has modified this record, do you want to Save changes or discard etc.'

If you say 'Yes, save changes' it screws things up mightily. The company record for the company in question is deleted! Doing this means that contacts for that company (in the Contacts table) no longer have a Company they relate to - so Access breaks the relationship and removes the Primary Key from the CompanyID field in the Companies Table.

Why or how it can do this one can only speculate.

I dread to think what will happen when I make a mde file of this and different users are doing different things. Access has a locking file to control this - but it doesn't seem to work very well. There is only one user at the moment and it can't track the changes made by one user.

Anyone else ever come across this? What is the solution? For now I am going to have to save the record on MainForm before opening any forms from it. Will this do it I wonder?