Solved Sequential numbers (1 Viewer)

[twgonder]

Okay, I've been warned to link to cross posting and here is the one I just made with Richard Rost's site:
Sequential Annual Coding in Microsoft Access (599cd.com)
I don't think it will get answered anytime soon, I was mostly responding to one of his videos.

I won't repeat what I wrote there, it's a good enough explanation, I think.

I'm not sure, but I'm willing to wager that a DMax will cruise the entire table looking for the max of a field if not indexed.
Not optimal and not what I need since users may add text to the sequential number for their purposes.
In this case, I'm adding a sequential number for records that have a random autonumber, so users aren't dependent on the autonumber for reference.
I may decide to add the year to the sequential number.
Users might add an "E" to a sequential number to signify it's an employee, or "C" for customer, etc.
I let them choose how the reference their "Entity" table for reference other than the primary ID (just no dupes).

So, has anyone taken the time to write a real routine for Access to get sequential numbers from a table as described (in the linked article)?
In my old db BASIC it was about fifteen lines of code, but I have no idea where to properly start in VBA.

 

[Minty]

I guess the $64000 question would have to be what determines the order of the records for this sequential number.

And what happens if something alters that potential business rule? e.g. a record added out of sequence? a record gets deleted?
Do you want to renumber the whole set of records? How does that affect the client's interaction with this sequential number?
Does any of the above really matter?

This really all boils down to what purpose does it really serve, and is it worth the effort? Is it purely for display purposes to be thrown away later?

 

[GK in the UK]

I adapted this code. You can code it to have any kind of prefix/suffix you like. You need a table to maintain the last picked number for each document type. Built for multiple users but my implementation doesn't test that. What it doesn't do, is recover the number for a deleted record. In financial applications this wouldn't be allowed. The way it works for me is:

Start new order, invoice, payment record or whatever. Take input for header details. At the moment of adding an item line, BeforeUpdate runs because we're leaving the main form. Up until that point, I can cancel the new document. BeforeUpdate calls the unique number routine and assigns it into the header. At that point the header can be considered permanent because we must not have gaps in the sequence. Every document type in my application has its own independent sequence.

Why AutoNumber shouldn't be used in MultiUser databases (And How to ge the Unique Number) - Microsoft: Access Tables and Relationships FAQ - Tek-Tips

 

[MarkK]

A sequential number does not belong to your data, it belongs to the list in which your data appears. As a result, it is generally easiest to generate this number at list creation time. If you are working with a report, for instance, generate the sequence number in the report.

 

[twgonder]

I guess the $64000 question would have to be what determines the order of the records for this sequential number.

And what happens if something alters that potential business rule? e.g. a record added out of sequence? a record gets deleted?
...

It's just a reference number. The user can add/change to anything they want in the entry form.
I just default to a sequential number that they can use or not.
(However, for something like an order number, the user can't change these on a whim--so we have two types of usage. That depends on the form, the subroutine just returns a non used next sequential number from a table)
For the sake of argument, let's say the first record is 1001. The user lets it stand.
The next record defaults to 1002, but the user changes it to E1002. Fine.
The next record defaults to 1003, but the user changes it to 1004. Fine
The next record will try to default to 1004 (from the sequential table, but that reference exists so it will bump to 1005.

 

[twgonder]

I adapted this code. ...

Why AutoNumber shouldn't be used in MultiUser databases (And How to ge the Unique Number) - Microsoft: Access Tables and Relationships FAQ - Tek-Tips

If what MichaelRed says in the article is true, about two users being able to get the same autonumber for a table record at the same time, then we have a really big problem in the autonumber algorithm in Access. I'm so far using random autonumbers, and I'm the only person entering records.

But really, is this true about multiuser and autonumbers crashing? I started another thread, weeks ago, about having my own autonumbers to avoid clashes in a multiBE environment, and everyone went ape nuts on me. So, I went ahead and used random autonumbers with a prayer, and here we have this article saying clashes can happen in a singleBE multiuser db? WTF?!!! This rabbit hole is spiraling out of control. I'm back where I started before all the hate.

 

[sonic8]

If what MichaelRed says in the article is true,

It is not.

 

[twgonder]

If what MichaelRed says in the article is true, about two users being able to get the same autonumber for a table record at the same time, then we have a really big problem in the autonumber algorithm in Access. I'm so far using random autonumbers, and I'm the only person entering records.

But really, is this true about multiuser and autonumbers crashing? I started another thread, weeks ago, about having my own autonumbers to avoid clashes in a multiBE environment, and everyone went ape nuts on me. So, I went ahead and used random autonumbers with a prayer, and here we have this article saying clashes can happen in a singleBE multiuser db? WTF?!!! This rabbit hole is spiraling out of control. I'm back where I started before all the hate.

Okay, I've taken a breath, and maybe it's time to go back to my original plan of using my own IDs and not autonumbers. For one thing, I always thought it was silly of M$ to assign them when the record goes dirty and now before update. My old designs had host codes on all record IDs to avoid clashes across hosts (or in the case of client-server Access BEs).

@GK, have you used this type of ID logic for all your tables? Or do you sometimes use autonumbers for certain tables? Or do you buck the typical Access convention of using autonumbers for all tables and opt for so called "natural keys" for some tables (i.e. using ISO codes for countries)?

One of the concerns I had before was with append queries and how to create the custom IDs when importing foreign data, since with autonumbers Access handles all that automatically.

 

[GK in the UK]

I may have misunderstood your original question.

Almost all of my tables have meaningless autonumbers. as keys. Some have natural keys. But I don't use autonumbers for any business purpose.

Just as an aside, I have a table with a natural key which turned out to be a mistake, not because I ever need to change the key, but because searching and lookup routines have to be coded for both types of key.

I was posting a solution to generating a unique sequential number with whatever prefix/suffix you want. The author of the code I linked says that opening a recordset using dbDenyRead means that only one user can grab the next number from the table. I don't allow users to edit the generated number, they get what they're given.

I don't think Michael Red is saying that two users can grab the same autonumber for a PK. Allen Browne does say that Access does sometimes generate duplicates. However he does say it fails if the autonumber is used as a primary key. Which is the same as what Michael Red is saying, he's not saying you'll actually get two autonumber primary keys the same: "This will eventually cause an error "Duplicate value in Autonumber field".

allenbrowne.com

 

[The_Doc_Man]

It's just a reference number. The user can add/change to anything they want in the entry form.
I just default to a sequential number that they can use or not.
(However, for something like an order number, the user can't change these on a whim--so we have two types of usage. That depends on the form, the subroutine just returns a non used next sequential number from a table)
For the sake of argument, let's say the first record is 1001. The user lets it stand.
The next record defaults to 1002, but the user changes it to E1002. Fine.
The next record defaults to 1003, but the user changes it to 1004. Fine
The next record will try to default to 1004 (from the sequential table, but that reference exists so it will bump to 1005.

Forgive me if this comes across as harsh, but... This is totally wrong on so many fronts. If there is a reference number that could be used for some kind of lookup and it is editable, it is not a good reference number. If a user can change that number arbitrarily, then NOTHING ELSE can depend on that number. EVER. Neither child records that would use it as a foreign key nor the main table that would use it as a prime key.

This is a hypothetical and contrived case, but... Your user does something and gets reference 1002. The user changes it to E1002. Your defaulting method remembers 1002 because otherwise you could never get 1003 as the next number. Now time passes, then someone calls up and says, "My transaction seems to be wrong. The reference number is 1002." And you can't find that reference number because it has been changed by a user. Or imagine the question was "I can't find reference number E1002." Which, of course, you can't, because some user edited the number. To make it worse, if the user can change 1002 to E1002 then the field is a TEXT field - which makes it take up twice the room an ordinary numeric key would take. So it is inefficient. (That's a minor complaint.)

Whatever else it is, it is an example of giving a user a useless control that seems to mean something, but does not. This is the start of data obfuscation and user frustration. If you have a primary key on a record, whether you use autonumbering or a DMax+1 scheme or some other scheme, once that number has been generated AND COMMITTED as the PK for a record, it has to be immutable.

 

[CJ_London]

If what MichaelRed says in the article is true, about two users being able to get the same autonumber for a table record at the same time,

total hogwash. Easy enough to test.

Create a table with an autonumber PK and another field. Create two identical queries based on that table. Open both queries, in one of the enter a value in the other field but don't move to another record so it does not update and for a new table the PK will be populated with 1 Now move to the other query and enter a value in the other field, the PK will be 2.

So perhaps you think it might be timing? create another table and populate it with say 100000 records. use vba code to run an update query to append the new table to the old table. Whilst it is running manually add a record from one of the original queries. You will need 100000 records (perhaps a lot more) to give you time

Now have two apps, both linked to your original table and an append query in each. set both running at exactly the same time. I've attached an example you can use as a test.

This should demonstrate that one of the FE's gets 'control' of the table first and appends it's records. Once done, the other gets control and does the same - the appends are not interweaved.

The allen browne article is more comprehensive and shows the causes - the first two relate to .mdb's, not .accdb's the third can be avoided with good form design. With regards random autonumber and replication ID's then technically it is possible, but I've only ever seen it once in over 25 years of developing with two separate systems creating random ID's and trying to append to a master. Never seen it with a GUID (replicationID)- but per previous discussions with you on the subject, GUID's do not make good primary keys. And is why for synchronising, you need both - random autonumber for PK and replicationID as a 'backup' in the event of a clash

 

[The_Doc_Man]

To the best of my knowledge and understanding of lock dynamics, when the ACE engine goes to perform BE action queries, it takes out an exclusive write lock on the BE file. If an autonumber is involved (i.e. due to an INSERT INTO), the action includes updating the seed entry in the tabledef while that exclusive lock is still in place. ACE then quickly releases its locks on the BE file as quickly and quietly as possible. However, because its actions are done under an exclusive file lock, nobody else should be able to step in on that seed update. This "exclusive lock" mechanism is how the Windows file system linearizes access to critical resources. Access isn't doing it... WINDOWS is doing it.

In theory at least, the ONLY way to get an autonumber collision involves something that is (a) hyper-privileged, with the ability to act with the same access rights as a device driver and (b) both the privileged program and the other program that experiences the collision have to be local (on the same machine) because otherwise the collisions are coming in from the network and that would mean the (a) case CAN'T happen and (c) the program can do the same things that Access does to update an autonumber seed - but see below regarding "doing what Access does."

Acting like a device driver means the code COULD at least in theory choose to NOT honor the interlocks normally placed on device-level code that is meant to linearize control of that device. Code that doesn't have that level of privilege isn't permitted to issue a non-synchronizing I/O request. Further, these privileges are not like file permissions. These are registry-level HKLU settings that allow the local user the ability to perform special actions. Therefore, even two programs inserting records into the same table from two different machines using network-based I/O are prevented from simultaneously touching the tabledef autonumber seed property that controls autonumbers. The network "listener" synchronizes I/O scrupulously and does not have special privileges unless its user does ON THAT MACHINE.

The final part of the argument is that since Access is doing this, and it is written to ALWAYS honor I/O synchronization methods, it would take very strange circumstances to lead to an autonumbering collision even if working on a local copy. Having said that, such a collision has been reported on this forum a few times, but so far as I recall, always concomitant with database corruption of some kind.

 

[twgonder]

It is not.

Without looking at the source code for Access, how can you know it's not possible? A great test would be to fire of two or more large append queries at the same time against a BE table from two (or more) FEs and see what happens.

Forgive me if this comes across as harsh, but... This is totally wrong on so many fronts. If there is a reference number that could be used for some kind of lookup and it is editable, it is not a good reference number. If a user can change that number arbitrarily, then NOTHING ELSE can depend on that number. EVER. Neither child records that would use it as a foreign key nor the main table that would use it as a prime key.

This is a hypothetical and contrived case, but... Your user does something and gets reference 1002. The user changes it to E1002. Your defaulting method remembers 1002 because otherwise you could never get 1003 as the next number. Now time passes, then someone calls up and says, "My transaction seems to be wrong. The reference number is 1002." And you can't find that reference number because it has been changed by a user. Or imagine the question was "I can't find reference number E1002." Which, of course, you can't, because some user edited the number. To make it worse, if the user can change 1002 to E1002 then the field is a TEXT field - which makes it take up twice the room an ordinary numeric key would take. So it is inefficient. (That's a minor complaint.)

Whatever else it is, it is an example of giving a user a useless control that seems to mean something, but does not. This is the start of data obfuscation and user frustration. If you have a primary key on a record, whether you use autonumbering or a DMax+1 scheme or some other scheme, once that number has been generated AND COMMITTED as the PK for a record, it has to be immutable.

This video may explain better why I have a "reference" code/number rather than rely on the autonumber: Custom Sequential Numbers in Microsoft Access (599cd.com)

When it's something like a "people table" I'll let the client change the reference number they want for a person. Maybe they want national ID like most of the world. Or phone number or DL number. I don't care. The primary key is what I use for linking with foreign tables. My big question again, is do I want to rely on M$ autonumbers for multi-user and multi-BEs?

 

[The_Doc_Man]

I have relied on MS autonumbers for a database relating to U.S. Navy sensitive (machine security level) information and ran fine with it. You might guess that the Navy is not very forgiving when sensitive information is in play, but they allowed the usage and it never failed me. My current home project, a genealogy database, also uses autonumbers and it is doing fine, too.

As to multi-BE cases, you must understand that a PK in a table is only usable for relational integrity purposes for other tables within the same file as that first-mentioned table. Relational integrity cannot be enforced across file boundaries. Having said that, it is still possible to write queries based on JOINs that cross file boundaries. Just don't expect them to be quite as fast as a single BE file.

 

[CJ_London]

? A great test would be to fire of two or more large append queries at the same time against a BE table from two (or more) FEs and see what happens.

Provided in post #11

 

[twgonder]

Provided in post #11

Thanks, for some reason I didn't see your #11 until now. My web pages are getting a bit jumbled. I don't know if it's my unreliable ISP, Edge browser or this site.

 

[twgonder]

I have relied on MS autonumbers for a database relating to U.S. Navy sensitive (machine security level) information and ran fine with it. You might guess that the Navy is not very forgiving when sensitive information is in play, but they allowed the usage and it never failed me. My current home project, a genealogy database, also uses autonumbers and it is doing fine, too.

As to multi-BE cases, you must understand that a PK in a table is only usable for relational integrity purposes for other tables within the same file as that first-mentioned table. Relational integrity cannot be enforced across file boundaries. Having said that, it is still possible to write queries based on JOINs that cross file boundaries. Just don't expect them to be quite as fast as a single BE file.

I guess I should clarify again what I mean by multiple BEs. Store one has a BE. Store two has a BE. Region A for stores one and two has a BE. Ad nauseum. Corporate has a BE. All must synchronize at some point (either continuously or periodically). The problem I'm still struggling with conceptually is what to do when all those duplicate autonumbered records arrive at a centralized location for synchronization.

 

[arnelgp]

you mean consolidating all records into 1 table?
add another Field (signifying the Store).
then the Autonumber of each store should be Long number to the
consolidated table.
use insert query to insert the records.
now you can check for duplicate by Store+Long number.
even if there is duplicate (Store+Long number) does not mean they are.
you need to inspect each field if they are all same.

and i think on store level, they already ensure no
duplication occurs?

 

[CJ_London]

The problem I'm still struggling with conceptually is what to do when all those duplicate autonumbered records arrive at a centralized location for synchronization.

We've had this discussion before on AF - in summary

• you can use a multi field PK but that gets messy for multiple related tables (customer>invoice header>invoice line> etc).
• Or you have a synchronisation process to check for potential duplicates before syncing and develop a routine for handling them such as not import to the main table but to a 'reconcile' table for someone to decide what to do
• Using a random PK is fairly safe (but not foolproof)- as stated above in 25+ years, only ever had one clash - but recommend using a GUID just for syncing. The random number is determined based on time. The only way I can create a duplicate random number is by having two records created in the same table but in two different databases (e.g. StoreA and StoreB) at exactly the same time. Using a GUID (autonumber replicationID), there are no duplicates (tested on 1,000,000 records). But as previously advised GUID's do not make good PK's
• you need to be clear whether your syncing process is all 1 way i.e. all data input is from the stores and subsequently added to region then corporate. Or 2 way - so new customers are added at corporate and fed back down the line to stores whilst invoices go up the line. Or full syncing - Stores add customers - all fed up the line then back down to the other stores
• You also need to be clear who holds the 'master data'. So can all stores modify a customer name and that is then propagated across the rest of the system for example (in which case no-one holds the master data). But then perhaps there is a corporate pay/reward scheme which applies across the whole organisation

Depending on the requirements of your app - syncing 1 or 2 ways and/or for new data only, and/or to include changes in existing data and /or deletions will dictate what fields you need to manage the syncing process.

 

[twgonder]

you mean consolidating all records into 1 table?
add another Field (signifying the Store).
then the Autonumber of each store should be Long number to the
consolidated table.
use insert query to insert the records.
now you can check for duplicate by Store+Long number.
even if there is duplicate (Store+Long number) does not mean they are.
you need to inspect each field if they are all same.

and i think on store level, they already ensure no
duplication occurs?

A subset of all the BE data that has changed since the last synch needs to be aggregated and compared for changes (by the centralized "Data Momma"). The most recent version then sent to the field BEs. I played with composite keys for a BE ID to records, but that got complicated for the local BE with foreign key IDs. I tried adding a three digit BE code prefixed to something akin to autonumbers, but ran into a problem in that Access doesn't allow a function at some critical point for it all to work (I can't remember exactly what now).

As I explained in a thread before, Store 1 creates a "person" record (that is then used by the Customer table and a purchase). The person then goes to Store 2 and the record from Store 1 might or might not be there yet. A new person record might be created if the synch method was delayed. Later Store 2 might need to substitute Store 1's person record for their own (or vice versa) and any foreign records need their foreign keys adjusted for the modified ID (i.e. customer and order tables). I'm used to handling all this synchronizing with about three hundred lines of specific BASIC code and five fields in each table. Autonumbers, SQL, ADO, DAO all add a layer of complexity that I'm not accustomed to.

Depending on the table and how it's used, the "ownership" remains with the highest level BE (i.e. for records for Mr. Mrs. Sr.,III, Esq. countries, states, etc.) while other tables remain owned at the local BE (orders, timecard, etc.). This was all much easier when there was one brain that controlled everything (mainframe) vs. giving a piece of the brain to remote fingertips allowing them to do their own thing (client/server technology).