Whether in the single user db or in a multi-user db Auto Numbers are utilized to provide a unique id that you use to relate two tables and their records together only. You shouldn't depend on them as a piece of your data.  They don't get duplicated and don't get reused once they have been used and deleted.

In the case of multi-user environ it is up to you the developer / programmer to manage the relational integrity between records.  It is necessary to keep the RI for the sake of good clean data and to ensure valid records.

Other db systems such as SQL Server do not use the RI and Cascading Updates and Deletes
Why that is I can't tell you - only that it is and I / you just accept it and just deal with it.

I'm sure somewhere along the way Microsoft thought they were helping out inexperienced users but in reality it is one of those things that you need to learn the right way from the start.  Not grow to depend on a 'crutch' then have to re-learn when you get into more advanced topics.

Thanks for the reply.

Quote: "Other db systems such as SQL Server do not use the RI and Cascading Updates and Deletes
Why that is I can't tell you - only that it is and I / you just accept it and just deal with it. "

Basically how is RI done in the SQL Server environment if a direct RI function is not available? Seems like that would be ideal to setup Access that way now so in the future the transition could be made easily.


Quote: "They don't get duplicated and don't get reused once they have been used and deleted."

I could have sworn that was one of the reasons to make a record inactive versus deleting, to avoid reusing an auto-number.

Basically how is RI done... ?  
As I mentioned - programmatically by you the developer.  It is up to me to maintain the RI.
That is why Richard says don't rely on it.  He teaches early on not to depend on Accesses in built functionality because that way you don't have to make a transition.  You are already working with the tables correctly.

True on marking inactive.  BUT due to space, speed, etc. you may want or even need to delete records.
For instance an Order and the Order Detail records.  If the customer of that order moves or dies and you delete the customer
it would 'break' the referential integrity of the data.  That would alter sales data needed for taxes for instance.

Instead you might move the records to a history table then delete the records from the current table.  You wouldn't want to reuse deleted auto numbers because of existing data else where.

For me I just don't concern myself too much on the why is is that way.  I understand the concept, accept it, and work with it.  There are too many other things for me to learn and do and deal with to get the job at hand done.

My father used to tell me many many moons ago, "Don't sweat the small stuff".  

First off, Cascade UPDATES are dumb. That's basically saying if I change a Primary Key in one table, update all of the Foreign Keys in related tables to match... but we're using AutoNumbers for PKs, so that will never happen in any database you build in my classes. If you were using something dumb, like a Phone Number for someone's PK, then sure, you might want a Cascade Update if someone changes their phone number. But with AutoNumbers, don't worry about it.

Cascade DELETES are just dangerous. Be very careful with them. If you set up Cascade Delete between, say, Customers and Orders, and then someone accidentally deletes a customer, then BYE BYE there go all your orders for that customer and then your books are all screwed up. So I just don't like them for MOST situations. Sure, they're useful in a few temp-table situations, but... Meh.

I prefer to handle something that involves Cascade Deletes MYSELF with a little code. Want to delete an order? OK... ask the user if they're sure, use a little SQL to delete all the order details, then delete the order. A few lines of code. Everyone is happy. Of course, you shouldn't delete an order anyhow (mark it canceled).

Now as far as general Referential Integrity goes, the major benefit is that you can use it to prevent Orphans. For example, you can't add an order detail item without a parent order. You can't add an order without a customer. Those kinds of things are fine. But cascade UPDATES are dumb, and DELETES are dangerous and unnecessary.

You CAN use referential integrity in a multi-user or split database, however you cannot enforce RI between multiple database FILES. So as long as OrderT and OrderDetailT are in the same ACCDB file, you CAN enforce RI between them. If they're split into separate files, you cannot. Again, you'll have to handle that in your VBA/SQL code manually. Not super hard to do.

And yes, of course SQL Server does support RI, Cascade Updates and Deletes... but I don't use them there for the same reason I don't use them in Access. Dumb and dangerous. :)

Learn more in my Relationships video (RI in the extended cut) and in the Relationships Seminar.