Primary Key is being lost

[glenntb]

I have an Access table with "AccountNum" defined as a Primary Key (Indexed = Yes(No Duplicates)). Each day, I delete all records from this table, then recreate the list using a series of append queries. This process has worked well for a number of months. All of a sudden, I notice that my table is holding duplicate account numbers. The "AccountNum" field is no longer defined as a primary key! I redefine it as a primary key and re-run my process and all is well again.

Can anyone tell me why the field lost it's Primary Key designation and/or a way to ensure that it never loses it again?

Thanks in Advance!

Glenn

 

[Golom]

Are you compacting the database regularly?

With all that deleting and appending you will probably experience quite a bit of bloat in the database and that can eventually lead to corruption ... which is what this sounds like.

 

[glenntb]

The database option "Compact on close" is checked, so that the db gets compacted daily. I have seen occasional errors where compacting is not successful, do you think this is where the key is getting lost? Thanks!

Glenn

 

[lespaul]

have you tried to compact & repair the database? as always, make a backup first! I'm not sure if the repair runs automatically as part of the compact.....worth looking into.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[glenntb]

I haven't tried that - I will give that a try the next time I lose the key designation (it seems to happen 2-3 times /year).

Actually, is there a way to trigger a Compact & Repair via code? If so, I could schedule it to run once/month to try and prevent future problems. I haven't been able to find a method that works.

Thanks!

Glenn

 

[JerryKlmns]

I haven't been able to find a method that works.

Search in Tek-Tips, especially forum705

 

[MichaelRed]

Deleting all records in a table ... then compacting the .mdb file is known to affect the "Automumber" sequence. There are NUMEROUS threads in these fora (Tek-Tips) discussing this phenom. Please use search / advanced search to find / review hte existing body of literaturs. Further, "Autonumber" itself has been shown in some instances to have additional problems. At least one approach to avoidance of this issue is in the FAQ area(s), so please ALSO search thes areas for a possible work around.





MichaelRed

 

[glenntb]

Thanks Michael - I am not having a problem with autonumber, though. My unique key identifier already exists in the file extracts that I import fresh each day to ensure that I have the latest data. The issue that I am having is that the table that I am loading the data into, forgets that the "AccountNum" field is defined as a Primary Key (Indexed = Yes(No Duplicates)).

Thanks Jerry and Leslie - I will use the two db approach to compact db more regularly.

I will give it some time to see if the problem re-occurs, it usually happens 2-3 times / year, so I'll re-open the issue when it happens.

Thanks all!