Hi Ray, usually your Id column wouldn't be seen by the customer/user, it's something only the DB needs to know about, therefore it wouldn't matter if it skipped a number.
But as Rich mentions in the video if there are times you need it to always exist, like the PaymentTypes it can be better to maintain it yourself.

If you need to increment yourself you can use the Microsoft Access DMax Counter tip.

For the most part I do prefer using autonumbers. Generally they are meant for relationships only and the end user will never have to see them or work with them. If you need a number that the customer may see like a "customer code" then you can create your own, and we're going to be doing that in the POS that we're building. However that's a secondary number and the table will still have an autonumber. The exception is going to be that payment type table that's only going to have a couple of records in it and I want to hard-code things like credit card, cash, etc. in the VBA so I want them to have specific numbers.

As far as data recovery goes, it can be a pain if you lose some records and have to re-enter them and then have to reattach them to related tables. That can be a nightmare. In this case I recommend good backups. But it's nothing that you can't fix with a series of update queries. My general rule of thumb is to never delete information. Unless your database is getting crazy huge you should always just mark things as inactive or not needed. Don't delete orders for example.

As far as worrying about numbers being in sequence that's not something that you should have to worry about. As long as you have a unique number for your relationships that's all that matters. If you need sequential numbers then yes definitely use your own number. You can use DMAX to determine the largest current number, then add one. Save it to the table quickly though so another user doesn't get assigned that number in a multi-user environment.

You can get rid of autonumber gaps at the end of the table if you delete some records at the end. Just compact and repair the database and it will reset the autonumber to the next highest one.