Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2004
    Posts
    78

    Unanswered: Lost relationships & primary keys

    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.
    Any solution?

    Thanks

  2. #2
    Join Date
    Aug 2006
    Posts
    559
    Can you go into the table and delete the duplicates? Then save the table, close the db, reopen it, redo the relationships / joins, then have the user go back in?

    **Not too sure it'll work, but it may**

  3. #3
    Join Date
    Nov 2004
    Posts
    78
    Actually I have done this. But Im really concerned as to why this happened and how to prevent this from happening again.
    Thanks

  4. #4
    Join Date
    Aug 2006
    Posts
    559
    What does the user who last touched the DB know about MS Access? Have you put any security into the DB?

  5. #5
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    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,

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I've seen this before too. Every time I've encountered it, it's been a direct result of corruption.

    How complex is the database? Is it a split FE/BE design? Lots of memo fields? Number of tables?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Nov 2004
    Posts
    78
    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

    Any idea?
    Thanks

  8. #8
    Join Date
    Nov 2004
    Posts
    78
    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 Im really concerned as to why this happened and how to prevent this from happening again

    Thanks
    Joe

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    How many concurrent users?

    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.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Nov 2004
    Posts
    78
    4 users max
    There is no code that deletes tables/fields/indexes
    Thanks

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Well that is not a lot of users.

    Either way, the only way I know of to increase stability of data files is to migrate / upsize to SQL Server or similar.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •