- Local time
- Today, 18:18
- Joined
- Feb 19, 2002
- Messages
- 43,288
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.
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.
Custom Sequence Numbers
Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below
www.access-programmers.co.uk
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.
SQL Server Replication - SQL Server
Learn about replication in SQL Server, technologies for copying and distributing data and database objects among databases and synchronizing between databases.
learn.microsoft.com
I don't remember if there are other actual questions. Please let us know if there are any unresolved or unanswered issues.
