Solved Sequential numbers

We are 78 posts into this thread. Most of them are useless. A good thread is a short thread. It is time to shut it down because it is no longer useful as a reference. Several people answered your initial question regarding the difference between autonumbers and custom numbers and then we went down the rabbit hole of irrelevancies.

I'm pretty sure I posted this link earlier but I'm not sifting through the debris to find it so here it is again. It shows two uses for "sequence numbers". One as part of a custom unique ID and the other to number items in a child table with the option to change the sequence number to move the items around in the list as sometimes needs to happen if you are making lists of instructions for example.


Autonumbers are best used as the primary key for a table. They are unchangeable and they are numeric and provide a single-field relationship. If you also want a UNIQUE custom ID, make yourself happy. Just don't use it as a PK and don't make it random as in your example. You don't help the user by allowing him to be disorderly. That's how users lose things. In order to make it easy to work with, keep the parts separate as they are in my example and don't forget to make a unique index on the fields that are used to build the custom ID.

Although there is nothing wrong with multi-field primary keys, they make joins more complicated but there is a particular problem when using them with Access. That being combo and listboxes. If you want to be able to choose items from one of these controls, the RowSource query/table, MUST have a SINGLE field Unique Identifier or they will not work correctly. Believe me or not. Your choice. For that reason and that reason alone, I ALWAYS use autonumbers as PKs. I then create a multi-field unique index if I have a composite key required to support business logic.

You then diverted into off-line synchronization of BE's. As you see, there is no good way which is why MS stopped supporting the replication process with Jet and ACE some years ago. Your actual best option is to use SQL Server as the BE which does have a replication option but you have already discounted SQL Server as something that brings no value to this process except you are wrong about that. The problem might be that only the Enterprise edition of SQL Server supports replication. I'm pretty sure that the free version does not support it so if money is an issue then SQL Server won't solve your problem.


I don't remember if there are other actual questions. Please let us know if there are any unresolved or unanswered issues.
 
We are 78 posts into this thread. Most of them are useless.
....
I don't remember if there are other actual questions. Please let us know if there are any unresolved or unanswered issues.
Thanks for your comment. Yes, as stated previously, I've been looking at the link and db you generously provided to the actual question. Modification and testing underway.

I'll agree many of the comments are useless, and I wouldn't object to their deletion. Other people may have the same business requirement I've presented, in fact, I've seen at least three other threads on this Forum, with pretty much the same requirements, but one complex one, where this thread went sideways, didn't have a solution either.
 
Last edited:
If you still need additional help, I recommend starting a new thread with a clear question. Link to this one or any other thread if you think another thread has relevant material. Then mark this one as complete to discourage further discussion here.
 

Users who are viewing this thread

Back
Top Bottom