Strange Autonumber Error

[GKJR]

I am using Access 2010. This happened to me a few months ago and I was able to fix the problem, but I am wondering if anyone has ever seen anything like it before. I have a table for work orders and a related table for work order updates. I use a continuous form to browse work orders with a button in each record that opens another continuous child form for viewing/entering updates. There are about 2500 work orders and 7500 updates. My coworker was updating a work order in the child form and somehow the parent WorkOrderID field was changed to the new UpdateID value. I had a WorkOrderID value over 7500 when there were only about 2500 work orders in my table. It is an auto number field and as far as I know these values cannot be changed. The relationship was enforced between the two tables and this error destroyed my referential integrity. I had to write a procedure to cross check every single update to make sure it corresponded to an existing work order and sure enough I had several updates whose original WorkOrderID no longer corresponded to a work order. I reassigned these to a dummy work order and compacted the database and it seemed ok. I also had to re-establish my relationship.

[mshmyob]

I believe you are correct. I do not believe you can actually overwrite the PK autonumber value in the Orders table. If anything the system created a new record and copied the data and then deleted the original record somehow.

Without more information it would be hard to analyze.

Could you at a minimum supply the schema for your Order table and Order Update table along with any bridge tables, rules etc.

Cheers,

[NeoPa]

What's your question GK?

[GKJR]

I'm wondering if this was just some kind of fluke or if I did something bad that someone else has seen happen before. I've heard of databases getting corrupted, and when I first found this problem I thought that this had happened. It was not an easy task to fix it considering the amount of records I was dealing with. I couldn't manually cross check all of the updates to the work orders. In my mind auto-number fields have always been untouchable. You just set them up and you never have to worry about them again.

@mshmyob
I'm not really sure what you mean by the schema. There are other tables related to work orders and updates, but they're not intermediate. Work order updates has a direct reference to work orders. I don't really have that many validation rules set at the table level. I try to use combo boxes, list boxes, date pickers, etc in my forms as much as possible to validate data. It has been working for the past two years since I created it. I'll admit there was one other time that I found the relationship was destroyed because I don't think I had cascade deleting selected. When a work order got deleted there were updates that had a bad reference. I was able to fix that as well and I modified the new relationship to cascade deletes.

[NeoPa]

Sorry. From what I understand of your description this is not something I've seen happen naturally. I've seen AutoNumbers reset to continuing from earlier numbers before, but that doesn't sound like what you're describing.

[zmbd]

If you are running an update/append query, sometimes strange things can occur.

We would need to see the related code and sql.

When a work order got deleted there were updates that had a bad reference. I was able to fix that as well and I modified the new relationship to cascade deletes.

This is also a strange situation... if your parent records have child records, with a properly set relationship and referential integrity set, then you shouldn't be able to delete the parent until the children are deleted - I never set cascade delete just for this reason.
Can you tell me the steps you are taking to set the relationships between the tables?

So this leads me to - are different versions of Access opening the file?

[GKJR]

Hi zmbd,
I'm glad you're asking me this because I've always had an uncertain feeling about my relationships. It's a sneaking suspicion that maybe I did something wrong and don't know about it.

I don't always select Cascade Deletes for the join options, but sometimes I do because I feel comfortable that it will be ok for the particular relationship. In this case, I chose to change that option after the first incident I had so that I wouldn't have to deal with my original issue of referential integrity again. I also usually Cascade Updates, except in certain relationships where I'm using a list table and I don't think updates should be cascaded (where referential integrity isn't mandatory). I usually use the Lookup Wizard for creating relationships, but sometimes I just use the relationship window. I was never really sure how to manually change the option you get in the Lookup Wizard for "Restricting Deletes".

The database that we use at my work is the one I learned how to create and work with databases on. Sometimes I find mistakes in it that I made early on and I wonder what I was thinking. For the most part it works fine though. What I described above was the only bad incident I've had with it.

One thing that I read a while back and it seemed to stick was that relationships to PK autonumber fields should only capture the number value and not any additional columns. I think it was in some forum (not this one) and the author sounded pretty confident about it. He was saying that all of your validation could be done in Forms with RecordSource queries, which sounds fine to me. For a while I was creating new relationships like this, but I went back to the Lookup Wizard eventually as I didn't really see the value in this anymore.

[zmbd]

OK, here's the basics on table relationships:
Create, edit or delete a relationship ACC2010

Please note if you are using the " Lookup & Relationship Wizard" in the table design view, then you are creating lookup fields. Which serve a simular role for databases that will be published to a sharepoint site as normal relationships established thru the relationships tool; however, they are not as robust nor supported should you need to upscale later to SQL-Server, MYSQL, Oracle, (and I don't think that even Azure supports these). And the lookups seem to break every once in a while... Some of this is explained in the above link.

Cascade updates make very little sense in a properly normalized database and deletes are simply a waiting trap as you've ran into... worse, cascade deletes can destroy a vast amount of data (think the old dos command Del *.* (evil laugh). I think this blog entry by Tony Toews explains my position fairly well:

I have an intense dislike for cascade deletes in Microsoft Access. And I don't like cascade updates.

Cascade Updates
I don't like cascade updates. If you are using an autonumber primary key in your tables then cascade updates are a non issue because 1) you can't change an autonumber key and 2) the user should never see the autonumber key. If you are using a natural key, for example in Northwinds this would be using CategoryName as a primary key in the Categories table, then I can see this being somewhat useful. I don't care for natural keys though. Access needs some added functionality to support these better such as in the subform creation wizards.

Cascade Deletes
I have an intense dislike of cascading deletes. <smile> Especially when a user sees the Access message they don't pay any attention to the extra wording. And it can be way, way too easy to start deleting records from too many tables. For example you go to delete a customer and, whoops, cascade delete just removed all the customers invoice headers and invoice deletes. Clearly that's an extreme example but is quite possible if someone doesn't clearly understand what could go wrong.

(please use the link above to read the remaining blog entry)

Search here on Bytes for the Evils of lookup fields, one such thread is http://bytes.com/topic/access/answer...ds-tables-evil this comes up fairly often.

IMHO: Unless publishing to a Sharpoint site, where the use of lookups is required, it is best practice to not use them at the table level.

I think that once you get all of this straightened out in your database things like what happened to you here wont "just happen" anymore.

[GKJR]

Now I see that the "Restrict Delete" property is actually the same as enforcing referential integrity. The reasons for not using Cascade Delete and Cascade Update are very clear as well. I'm going to change around my relationships and get rid of my Lookup Fields. I have a table for Departments that doesn't use an autonumber primary key, so I'll keep that one set to Cascade Updates. I'm also going to get rid of any cascading deletes.

This was all so confusing to me when I first started working with Access. I wish Microsoft would just abandon the Lookup Field and just force people to use the proper references.