Table continues to corrupt

[thart21]

My table has been corrupting every week and there is only 2-3 people using it on a consistent basis. It will work fine to add new records for a couple of days then they will receive the "duplicate record" error message and it is trying to assign an autonumber that is already being used in the table.

It seems to be picking a random record to restart the autonumber with. I tested it by trying to add a record-it would assign id# 1068 (which is already used) I would get the error, get out, go back into the table and add another record and it would try to assign it #1069 and so on. When it got to a number that wasn't in the table #1071, it would allow the record then. It continues to go through this process, error-ing on id#1072. The record it starts with doesn't seem to have any bad data.

Does anyone know what would cause this to keep happening? I do have another table that ties the id #'s to this first table. When I move records from the first table into another one, or delete them completely, I haven't been removing the data from the second table. Could this cause a problem?

I would appreciate any help with this. I'm am currently working to get a SQL backend done in hopes this will solve these corruption issues.

Thanks,

Toni

 

[Argonak]

I dont know what could be causing this, but in my projects I've preffered to create my own identification number rather than using an automatic. My insertions don't tend to be too high ussually, so I use a random number and check it before inserting. If you have a high enough ceiling though its almost superfluous, I set mine to track the collision rate and have yet to actually have any collisions.

 

[Len Boorman]

Are you attempting to add records directly into the table and not via a form.

If you are then perhaps

You have the table open and so do I (Assume)

I add a record but it is not shown to you because teh table needs a refresh.

You try to add record and get error

Just a thought and not certain that this is probelm, just an idea


Len