Solved Sequential numbers

[twgonder]

This article starts with an erroneous premise - that being that autonumbers generate duplicates. They don't-ever. If you have an autonumber that is generating duplicates, the table is corrupted and the seed needs to be fixed to resolve the problem. Look for reset seed code posted here.
...

Here's a sample that includes two types of generated numbers and how you generate each.

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

Perhaps an erroneous premise, but then the question then is, why did he write the article and all that code?
Granted, it's a bit dated, but so is Access I suppose. Not much change for several years, just the typical M$ moving shit around. So, I'll suspect the bugs are still there.
My original post here was not related to the older problem of synchronizing BEs. I got dragged here by misunderstanding of a few.
For the old problem, I've coded a few solutions. My problem so far has been implementing them around the limitations of Access. It's been several weeks since I was working on this, with lots of attention in other parts of Access recently, but the big limitation I hit was functions weren't allowed in some critical feature, I even found that stated in the M$ documentation, but at the moment I can't remember exactly what it was.
But thanks for the link, I'll look for ideas there to better my code.

 

[CJ_London]

Have you considered using a SharePoint table simply to assign IDs for new customers?

OP want's to use his own take on 20+ year old methods

why did he write the article and all that code?

plenty of articles out there that are rubbish. Maybe 22+ years ago it was valid.. Since then there has been at least one significant upgrade to .mdb's and 3 to .accdb's. See this link.https://fmsinc.com/MicrosoftAccess/history/features.htm. Not much left of access 2000 or earlier.

If you are going to take these to heart at least have the sense to look at something more current.

 

[twgonder]

OP want's to use his own take on 20+ year old methods

plenty of articles out there that are rubbish. Maybe 22+ years ago it was valid.. Since then there has been at least one significant upgrade to .mdb's and 3 to .accdb's. See this link.https://fmsinc.com/MicrosoftAccess/history/features.htm. Not much left of access 2000 or earlier.

If you are going to take these to heart at least have the sense to look at something more current.

What can I say? One of the posters here brought the article to my attention, and he's been dealing with the same thing recently.
I haven't seen anyone offer a better solution for Access. And actually, the concept is older than forty years, too bad M$ didn't take it to heart when designing autonumbers, which btw, are even older technology as implemented in Access.

 

[twgonder]

Bring it. You post questions on here, some of them VERY basic, you're advised the best practice to achieve what YOU asked for, then you counter with an invalid reason why that won't work, despite what some of the best Access/VBA devs on the PLANET tell you...

And what is really frustrating, is that you want to "sell your resume", in some vain attempt to make you appear more knowledgeable than you actually are, thereby yielding the opposite results.

The last thing I would ever want to do is make any thread hostile, but you sir, are EXHAUSTING. Normally I would simply ignore your posts, but they ARE entertaining - in an exhausting sort of way.

I don't need to prove anything. I retired early with my buyout in the bank from the dot-com foolishness.
However, I see you want to ride on the coat tails of others and claim they are "best practices", when I've clearly shown the faulty logic therein.
I'm just trying to help a friend find a solution to a business problem in an environment that doesn't support cloud computing and 100% Internet.
Oh, there's another new-fangled technology that flamed out once before in the 80s.
Well, you're doing a good job at the last thing you would ever want to do, SR.

 

[NauticalGent]

However, I see you want to ride on the coat tails of others and claim they are "best practices",

That is how it is done in this industry and if you were not trying to do the same, you would not be here.

when I've clearly shown the faulty logic therein.

No, you haven't.

 

[CJ_London]

What can I say? One of the posters here brought the article to my attention, and he's been dealing with the same thing recently.

perhaps one of the many 'players' you refer to. Sorry, you are just inconsistent with what your objective is, what problems you face and it seems you struggle to tell the difference between someone who knows what they are talking about and someone who doesn't.

Seems to me you are trying to build an app with enterprise scale ambitions and zero infrastructure costs. Nothing wrong with that., Access is great as a front end, but the backend is simply not enterprise scale, you need to use sql azure, sql server, mySQL, whatever.

If you have the issue of poor connectivity, then you need replication, not synchronisation.

Replication vs Synchronization - Active Directory Security Windows Server 2003

The first important concept to understand when looking at AD updates is the difference between replication and synchronization. These two terms are often used

www.serverbrain.org

but your biggest problem would appear to be timing.

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).

auto numbers of any sort are not going to help you with this. Your first problem is identifying you have a duplicate person record from two different stores. Can't rely on the persons name, perhaps their SS or payroll number? Or perhaps a better solution is they are issued with a staff card, either magnetic or with a bar or Q code printed on it and they just scan it in when serving a customer. Not relevant if a customer visits store A then store B, you would need a different solution for that.

 

[twgonder]

perhaps one of the many 'players' you refer to. Sorry, you are just inconsistent with what your objective is, what problems you face and it seems you struggle to tell the difference between someone who knows what they are talking about and someone who doesn't.

Seems to me you are trying to build an app with enterprise scale ambitions and zero infrastructure costs. Nothing wrong with that., Access is great as a front end, but the backend is simply not enterprise scale, you need to use sql azure, sql server, mySQL, whatever.

If you have the issue of poor connectivity, then you need replication, not synchronisation.

Replication vs Synchronization - Active Directory Security Windows Server 2003

The first important concept to understand when looking at AD updates is the difference between replication and synchronization. These two terms are often used

www.serverbrain.org

but your biggest problem would appear to be timing.

auto numbers of any sort are not going to help you with this. Your first problem is identifying you have a duplicate person record from two different stores. Can't rely on the persons name, perhaps their SS or payroll number? Or perhaps a better solution is they are issued with a staff card, either magnetic or with a bar or Q code printed on it and they just scan it in when serving a customer. Not relevant if a customer visits store A then store B, you would need a different solution for that.

Of course, my strategy changes as I learn more about what Access can and can't do. My objective remains fixed.
Yep, people in Colombia don't have the resources that "gringos" have. A good programmer here makes about US$4 /hr.
Mid-size business (50-100 employees) can only dream to break a gross of US$1,000,000.
Yep, I've known for some time that autonumbers are going to be a problem. It's very old technology.
The records still need a primary ID that is independent of any personal information.
Adding that personal information as a second reference field is how this thread started, before it went sideways, again.
Microsoft's definitions for AD are not the industry standard for dbs. I know the difference.
As I mentioned before by example, the design will have a hybrid of both replication and synchronization.
An open question is how much I'm allowing remote people to add to the replicated tables for later synchronization with the entire BE network.
Unfortunately, the answer, I'm sure, will be yes-everything! And that's the mess I have to plan for in a distributed, client/server environment.

 

[twgonder]

That is how it is done in this industry and if you were not trying to do the same, you would not be here.


No, you haven't.

I'll just say: #1) You didn't understand. #2) You haven't understood. It's all there, but you'll need reading comprehension skills instead of scrub the latrine with a toothbrush experience.

 

[NauticalGent]

I'll just say: #1) You didn't understand. #2) You haven't understood. It's all there, but you'll need reading comprehension skills instead of scrub the latrine with a toothbrush experience.

You seem to be the one with comprehension skills. They have written the explanation in braille and shoved it up your arse, yet you are still clueless.

 

[GK in the UK]

I'm kind of wishing I hadn't linked the article, yes it's old but it's something I discovered as an Access beginner and it seemed to fit the bill. The dbDenyRead was a kind of 'ah-ha' moment. Does no-one use this? The issue of duplicated Autonumbers mentioned in the article is really a red herring to the purpose of that routine. As far as I understand it's not meant to generate unique PKs it's meant to generate unique references in a multi user environment.

@Pat, thank you for the link to your Custom Sequence Routine. It's way simpler than the one I linked to but It doesn't prevent duplicate references, does it? I think you state that on the splash form. My version of Michael Red's code is already much simpler than the code posted because my reference construction is simpler.

As far as I can see it will do items 1-6 in the OPs linked question, except 4 (Test is done to make sure a record for the new sequence isn't already existing (if so add one and repeat) because he wants the ability for users to change the reference. OK, he got some heat for that but if it happens once only at creation time I see nothing wrong with it.

someone who doesn't.

Ouch, I think that's me told.

 

[Pat Hartman]

why did he write the article and all that code?

Because he thought Access had made a mistake and he never understood the problem.

claim they are "best practices", when I've clearly shown the faulty logic therein.

I don't see where you've won any logic points anywhere in this thread.

Yep, I've known for some time that autonumbers are going to be a problem. It's very old technology.

Still in use by SQL Server, Oracle, MySQL et al so don't disparage jet and ACE for that. You've already been told to not use Jet or ACE for your application.

It's way simpler than the one I linked to but It doesn't prevent duplicate references, does it? I think you state that on the splash form. My version of Michael Red's code is already much simpler than the code posted because my reference construction is simpler.

No it doesn't. I think I mention that somewhere. If you have a high-speed data entry application where dozens of transactions are added every minute, you could run into duplicates but it really depends on what your constricting values are for the sequence. I've posted suggestions in numerous threads on how to solve the problem by intercepting the "duplicate" message and looping to try again. I didn't post code because the timing is different depending on whether your BE is Jet/ACE or some RDBMS, plus very few applications here are so busy that this would be a common problem.

1-6 in the OPs linked question, except 4 (Test is done to make sure a record for the new sequence isn't already existing (if so add one and repeat) because he wants the ability for users to change the reference.

It doesn't allow duplicates to be added provided you've made the CustomSeqNum unique and required so no bad record could be added but it does not take the next step of the loop.

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.

Of course, what else would it do? That is why the sequence number is a separate field in a multi-field index.

The next record defaults to 1002, but the user changes it to E1002. Fine.

NO, It is NOT FINE. A sequence number is just that. A NUMBER that is larger (usually by 1 but the gap can be larger as you'll see if you look at my examples where I make the gap 10 to allow for manual inserts). If you want to add letters, you need to use separate fields and define rules. You can't just randomly add letters to a numeric field. That is what you do in a spreadsheet, not a relational database.

For one thing, I always thought it was silly of M$ to assign them when the record goes dirty and now before update.

Because you don't understand the way Access works. The point of the autonumber is to be a UNIQUE identifier, Not an actual sequence number so it makes no difference whatsoever if gaps occur. Also, if you use forms bound to other RDBMS' you will notice that the Identity column (what SQL Server calls an autonumber) is not immediately assigned. It is NOT assigned until Access sends the append statement to the server and the server returns the assigned Identity value.

My old designs had host codes on all record IDs to avoid clashes across hosts (or in the case of client-server Access BEs).

If you have an application with separate BE's consolidation becomes a problem. I've had instances where I needed disconnected updates. That means that all databases need a unique code and a sequence number. You would NEVER use an autonumber in this situation. You would use custom generated unique IDs with two fields. The DBID and the SeqNum. You can use an autonumber as the PK for convenience but in the consolidated table, the PK is the DBID + SeqNum and the reference number becomes the autonumber to the disconnected db.

My big question again, is do I want to rely on M$ autonumbers for multi-user and multi-BEs?

Autonumbers are not a problem for PKs for multi-user applications. Multi-BE applications are extremely rare. For synchronization reasons, you can't use autonumbers. You need to use a two-field PK comprised of a field to identify a source database and a sequence number to identify the specific record in that table. Technically, the sequence number could be an autonumber. The problem comes when you combine the sources into the master. Obviously, autonumbers generated in one db will conflict with autonumbers in another so you need a two-field UniqueIndex. Then the master table can actually use an autonumber as the PK. You just have to sync the child records when appending from the remote to the master databases. Not that hard. If you don't use the autonumber as part of the unique index in the remote table, then you should include the autonumber to ease referencing to the child tables.

Working on it. Most of the "experts" have no idea what I'm talking about, as MOST, I presume never have worked in the environments I have. They "play" with access but haven't done an enterprise application.

How patronizing can you get? You are talking to people who have developed applications for the military and for Fortune 100 companies with thousands of concurrent users and millions of rows in tables. It has been more than 20 years since I've personally had to worry about sync'ing disconnected databases. In this networked world, it doesn't happen any more. Everyone has an internet connection and if push comes to shove and you are worried about a new customer going across town to a different store and expecting his customer account to be already active, I think you probably need to get a little more realistic in the problems you create. What is stopping the form that creates a new account from reporting that via email to the mothership? The mothership can process email every minute to ensure nothing slips by.

Let's start with something you might understand, and my second issue (they kept the first) of the USCG license I got at age 19, being the youngest and first ever under age 30 to pass on the first try with a practical exam that was 80 years old. The Lt. decided he wanted to F with me because my dad, an ex-Coastie, had on his "ticket" unlimited tonnage, any ocean. We can continue with my time at USAFA, the pilot certificates, and finally my business in Los Angeles. We can skip my time in Congress, as that has nothing to do with technical abilities.

OMG. I'm so impressed with your credentials. It is like being looked down upon by royalty. I'm not sure why you deign to waste your time even talking to us. What could we possibly tell you.

and how did the dumb terminals of mainframes handle that?

They didn't handle it at all if they had no network connected. They couldn't even carry on with local processing

I've seen it estimated that 70%+ of business still use legacy applications. Now why is that?

Because they still work? So why spend millions to recreate them in some new technology?

 

[The_Doc_Man]

OK, ALL OF YOU, stop this. There is a lot of member disrespect bubbling up here. I go away to have lunch with my stepson and grandson, and when I come back it has turned into a donnybrook.

@twgonder - you are tearing down methods that many Access experts are telling you would perhaps work.

Responding members - you are all dancing around the mulberry bush, too!

The problem, twgonder, is that you want your cake and want to eat it too, but in the world of technology you can't do that. You say you cannot link everything together so that true synchronization is possible because of distant stores in different locations. Access rides TCP/IP protocols that require robust connections. You don't have that and say you can't get it.

Your solution is to find something that doesn't require robust protocols. It exists - and is called the WEB. You don't want Access. You want a web solution that doesn't depend on robust connectivity.

 

[NauticalGent]

I know I took it to this low you speak of, but somebody had to say what everybody wanted to but were too polite to do so. If it results in a ban for a few days, I'll happily take it.

 

[twgonder]

You seem to be the one with comprehension skills. They have written the explanation in braille and shoved it up your arse, yet you are still clueless.

Okay smart guy, what was my question and what do you think was the suggestion that will work?

 

[twgonder]

Because he thought Access had made a mistake and he never understood the problem.

I don't see where you've won any logic points anywhere in this thread.

Still in use by SQL Server, Oracle, MySQL et al so don't disparage jet and ACE for that. You've already been told to not use Jet or ACE for your application.


No it doesn't. I think I mention that somewhere. If you have a high-speed data entry application where dozens of transactions are added every minute, you could run into duplicates but it really depends on what your constricting values are for the sequence. I've posted suggestions in numerous threads on how to solve the problem by intercepting the "duplicate" message and looping to try again. I didn't post code because the timing is different depending on whether your BE is Jet/ACE or some RDBMS, plus very few applications here are so busy that this would be a common problem.

It doesn't allow duplicates to be added provided you've made the CustomSeqNum unique and required so no bad record could be added but it does not take the next step of the loop.

Of course, what else would it do? That is why the sequence number is a separate field in a multi-field index.

NO, It is NOT FINE. A sequence number is just that. A NUMBER that is larger (usually by 1 but the gap can be larger as you'll see if you look at my examples where I make the gap 10 to allow for manual inserts). If you want to add letters, you need to use separate fields and define rules. You can't just randomly add letters to a numeric field. That is what you do in a spreadsheet, not a relational database.

Because you don't understand the way Access works. The point of the autonumber is to be a UNIQUE identifier, Not an actual sequence number so it makes no difference whatsoever if gaps occur. Also, if you use forms bound to other RDBMS' you will notice that the Identity column (what SQL Server calls an autonumber) is not immediately assigned. It is NOT assigned until Access sends the append statement to the server and the server returns the assigned Identity value.

If you have an application with separate BE's consolidation becomes a problem. I've had instances where I needed disconnected updates. That means that all databases need a unique code and a sequence number. You would NEVER use an autonumber in this situation. You would use custom generated unique IDs with two fields. The DBID and the SeqNum. You can use an autonumber as the PK for convenience but in the consolidated table, the PK is the DBID + SeqNum and the reference number becomes the autonumber to the disconnected db.

Autonumbers are not a problem for PKs for multi-user applications. Multi-BE applications are extremely rare. For synchronization reasons, you can't use autonumbers. You need to use a two-field PK comprised of a field to identify a source database and a sequence number to identify the specific record in that table. Technically, the sequence number could be an autonumber. The problem comes when you combine the sources into the master. Obviously, autonumbers generated in one db will conflict with autonumbers in another so you need a two-field UniqueIndex. Then the master table can actually use an autonumber as the PK. You just have to sync the child records when appending from the remote to the master databases. Not that hard. If you don't use the autonumber as part of the unique index in the remote table, then you should include the autonumber to ease referencing to the child tables.

How patronizing can you get? You are talking to people who have developed applications for the military and for Fortune 100 companies with thousands of concurrent users and millions of rows in tables. It has been more than 20 years since I've personally had to worry about sync'ing disconnected databases. In this networked world, it doesn't happen any more. Everyone has an internet connection and if push comes to shove and you are worried about a new customer going across town to a different store and expecting his customer account to be already active, I think you probably need to get a little more realistic in the problems you create. What is stopping the form that creates a new account from reporting that via email to the mothership? The mothership can process email every minute to ensure nothing slips by.

OMG. I'm so impressed with your credentials. It is like being looked down upon by royalty. I'm not sure why you deign to waste your time even talking to us. What could we possibly tell you.


They didn't handle it at all if they had no network connected. They couldn't even carry on with local processing

Because they still work? So why spend millions to recreate them in some new technology?

Thanks for your comments. I don't think some of the quotes are from me. I don't think I ever said some of what you say I did. I can't read all of the post, as once again this site, or Edge has made a mess of what I see.

As to the original question, it's a reference number or code for the client to use other than the autonumber for record identification. I'm just supplying a sequential number as a starting point, if they don't want to use something else. It's a required field, it's what's shown in documents, I don't show autonumbers. Why do you think it can't change?

My credentials were for that bloke that's spoken of his "nautical experience" and speaks crap about his brother. What a fine fellow. Why do you think it was meant for you?

Let's forget about the BEs and synching, I didn't want to go there but someone asked a why question. It's been beaten to death elsewhere, and I have yet to find a solution, but I think some of the links provided may get me closer.

As to autonumbers, it was probably the first code ever written on a computer. Start with one and add one. Over the years far more sophisticated key algorithms have been implemented, even if simple sequential numbers are still available in most systems

 

[NauticalGent]

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.

This was your original question, which everyone took the time to explain why it was a bad idea. You spent more time trying to debunk what was being explained to you than trying to understand what they were saying.

CJL put it very eloquently with his sailing ships/motorized vehicle analogy but you completely ignored it. He even provided a demo as a proof of concept - did you even try it? If so, you never reported back how it performed, let alone say thank you. DOC and Pat broke it down even more for you and all they got for their efforts were insults ("experts").

At some point, no one here is going to waste their time on you - except Doc, he is a glutton for punishment.

 

[KitaYama]

the simple answer is that I'm not in the USA where I can count on 100% connection time. Stores that have twenty cashiers need to keep selling even if a road crew destroys a cable and replacement/connection is a week out.

I'm just trying to help a friend find a solution to a business problem in an environment that doesn't support cloud computing and 100% Internet.

I hope you forgive a stupid question. I'm trying hard to understand what you're planning to do.
If you don't have a good internet connection, how do you plan to sync your main server with your multi back ends?
Saving on DVD and sending them by post?
Or waiting for the time when the internet is back? In this case what will happen while your multi BEs are not synced? Because as you say it may be a week ahead to replace the cables.

And another question if you don't mind.
Why you use $ instead of S in MS? Because I never understand why people who don't like Microsoft, keep using its products.

 

[NauticalGent]

Because I never understand why people who don't like Microsoft, keep using its products.

I can honestly say, as far as MY situation goes, I have no choice. The US Gov provides the tools I use. I personally like MS products, but MS has really left us Access lovers twisting in the wind.

 

[KitaYama]

I can honestly say, as far as MY situation goes, I have no choice. The US Gov provides the tools I use. I personally like MS products, but MS has really left us Access lovers twisting in the wind.

Just out of curiosity.
What would you choose instead of Access if you were free to choose?

 

[twgonder]

This was your original question, which everyone took the time to explain why it was a bad idea. You spent more time trying to debunk what was being explained to you than trying to understand what they were saying.

CJL put it very eloquently with his sailing ships/motorized vehicle analogy but you completely ignored it. He even provided a demo as a proof of concept - did you even try it? If so, you never reported back how it performed, let alone say thank you. DOC and Pat broke it down even more for you and all they got for their efforts were insults ("experts").

At some point, no one here is going to waste their time on you - except Doc, he is a glutton for punishment.

Again you don't understand, or didn't go watch the original linked tip. If sequential numbers are so bad, then why is practically everyone using then as a primary Key? And my original question was about sequential numbers in a reference field, not the primary key. You also missed where I showed, before, how autonumbers are the old sailing ship, not something of the jet age.

And you missed the links that people did provide to answer my question. Some very qualified people I might say. So, your comments don't hold water. It's when we went back into the autonumber and synching remote BEs, again, where things went nuts yet again. And some people refuse to accept that the whole world isn't up on the Internet 100% of the time. And even if it was, I've been told by some very qualified people that Access doesn't do well over WANs. So, anything suggesting that solution is, again, missing the point.

Yes I did try the CJL demo, and I'm working on adapting to more approximate what I expect to accomplish. I even thanked him for it, but you obviously missed that too.

I'm still waiting for your brilliant solution to any of this., rather than just parroting things you don't understand.