I do no t know why I suddenly lost the joins/relationships between many tables as well as some primary keys? This allowed the user to enter the same value in the primary key field and I can not redo the join since there are duplications.
Could I ask what is the nature of your primary keys ? If they are codes or names, then users may have edited them, unwittingly destroying the relationship. Personally, I ALWAYS use Autonumber fields as primary keys and long integers as the corresponding foreign keys. In most cases, they do not even need to appear in forms, or if they do, they can be rendered invisible. If the database is being used by the user community for analysis via queries and reports, etc. i do make one concession to the usual normalisation rules. With each foreign key (ie. expressing a relationship) I include another code or name, so that the user can see whence the relationship derives. If the copy of a code is accidentally changed, there is no great harm done and I run periodical refresher functions to correct any anomalies which have arisen,
There are 67 tables; 10-15 are small lookup tables.
Total records (all tables) about 230,000
BE size = 37 MB
Primary keys are auto numbers and is hidden from the uses
DB has FE/BE
Users do not have BE access only forms, not even to query design
Maybe total 10-15 memo fields
On July 2008 I manually removed all duplications and fixed all relationships and primary keys.
Recently the uses called me again that they see duplicated records. I checked the BE and I see that I Lost relationships & primary keys again.
I fixed it again.
But I’m really concerned as to why this happened and how to prevent this from happening again
Sounds to me like you need to go to SQL SERVER or MySQL... unless your code deletes tables/fields/indexes, the only reason relationships and keys would go missing is corruption or as a result of a compact and repair where the file is damaged.