Solved Sequential numbers

[twgonder]

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

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.

I just saw your post after submitting mine (#20). Yes, we've had this discussion before, and I'm aware of all the points you made. Despite that I remain without a solution that I am comfortable with. I've tackled some of your points on requirement, unknowingly, in post #20. I also did a lot of code/design testing over the course of three weeks on just this.

I'm not so trusting that random keys won't duplicate if created at different times. I'm going to modify your great little test in post #11 to test for this. But, one has to consider multiple BEs all creating records in several tables over the course of several years. The only way I can think to overcome this is with the old method I used of a "Server code" (like 201 for Store 1 BE) and a sequential number combined to make a system wide key for each table. That's a guarantee there will never be a clash (I never had one in 20+ years and millions of transactions) even when the sequential seed was "lost/corrupted" (simple high/low iterative test to find where to begin anew).

Your db in post #11 is most welcome. It answers several different questions (and created new ones) that have "bugged" me the past few months. Thank you.

 

[cheekybuddha]

Why don't you just bite the bullet and use a single server based database that all the clients connect to?

 

[NauticalGent]

Why don't you just bite the bullet and use a single server based database that all the clients connect to?

Here we go...

 

[sonic8]

Without looking at the source code for Access, how can you know it's not possible?

Because the documentation says so and I have no reason to doubt it.

It is possible for auto number to generate duplicate values. However, this is caused by corruption of the database file and when this happens it is not a multi-user concurrency problem, but a general problem also affecting a single user.

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.

You are the one doubting the documentation and the knowledge of numerous experts. So, I'd say it's up to you to conduct that test.

 

[NauticalGent]

You are the one doubting the documentation and the knowledge of numerous experts. So, I'd say is up to you to conduct that test.

 

[twgonder]

Because the documentation says so and I have no reason to doubt it.

It is possible for auto number to generate duplicate values. However, this is caused by corruption of the database file and when this happens it is not a multi-user concurrency problem, but a general problem also affecting a single user.


You are the one doubting the documentation and the knowledge of numerous experts. So, I'd say it's up to you to conduct that test.

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. Just my educated guess from some of the comments. There's a big difference between developing a one-up application on PCs and writing one that spans several servers across the globe.

 

[twgonder]

View attachment 103375

Do you really want me to go there with you?

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.

 

[twgonder]

Why don't you just bite the bullet and use a single server based database that all the clients connect to?

Already answered in another post, 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.

 

[cheekybuddha]

Do the stores need to be updated (synced) with data from the other stores eventually (from the mothership db)?

 

[Minty]

I think I would be developing this on an azure backend if it's an enterprise-level application, and definitely not an access backend.
I would have a local SQL Server at each store doing the leg work, with a local Acccess front end.

You can then do all the syncing between the Azure Master database and the local SQL Server using suitably robust SQL Server tools.

It's not square peg - round hole, IMHO it's not an appropriate technology for the job.

 

[NauticalGent]

Do you really want me to go there with you?

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.

 

[NauticalGent]

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.

I rest my case...

 

[CJ_London]

Stores that have twenty cashiers need to keep selling even if a road crew destroys a cable and replacement/connection is a week out.

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

and how did the dumb terminals of mainframes handle that?

Really is time you stopped trying to apply the design concepts of sailing ships to motorised vessels.

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.

Personally I find that insulting - many of the apps I have developed are enterprise scaled and some of those globally. And I have experienced the scenarios you paint, but you insist on powering yours with wind.

 

[NauticalGent]

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.

Oh boy, you done done it now...

 

[twgonder]

and how did the dumb terminals of mainframes handle that?

Really is time you stopped trying to apply the design concepts of sailing ships to motorised vessels.


Personally I find that insulting - many of the apps I have developed are enterprise scaled and some of those globally. And I have experienced the scenarios you paint, but you insist on powering yours with wind.

That's the point, dumb terminals didn't do anything other than input/output data. Everything was centralized, like our brain.
Tall ships still draw big crowds. I've seen it estimated that 70%+ of business still use legacy applications. Now why is that?
One of my old clients, Revlon, bought into the SAP and Anderson consulting hype. Check their stock price over the past 25 years, when they decided that my legacy system wasn't good enough for the modern age.

I didn't include you in the "most" comment, you've been most helpful.

 

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

You can go paste some more code, you're an expert of course. Or swab a deck oh round one.
I'm still learning the (basic) Access ways. You got a problem with questions?
Yep, I never claimed to be an expert in Access. But I do know how to sniff out a solution. None of which I've seen come from you.
The only reason it's exhausting (for me too) is because of people like you, o round one,
I have to explain simple DB/business models three+ times, and some still don't get it.

You might want to do a little reading in psychology. We are all limited, insecure, square pegs in this life, and I can see you are no exception.

 

[NauticalGent]

You can go paste some more code, you're an expert of course.

No, I'm not. Nor have I never claimed to be.

Yep, I never claimed to be an expert in Access. But I do know how to sniff out a solution. None of which I've seen come from you.

Two things I do excel at is heeding advice when it is given and keeping silent to avoid exposing my ignorance - except when it come to engaging lost causes.

You're sniffing some sort of solution for sure, no doubt there...

 

[twgonder]

You're sniffing some sort of solution for sure, no doubt there...

Oh I give, I give, you're such a wit. (Half wit.)
If you are an example of round, I'll take square as a compliment.

 

[NauticalGent]

Oh I give, I give, you such a wit. (Half wit.)

Not too shabby, actually!

 

[Pat Hartman]

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

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.

What you are describing is a problem caused when asynchronous updates are allowed. It has nothing to do with Access. It is a problem regardless of what you use as a BE data store. There is no single solution to resolve this. How often do you reconcile the separate BE's? Why do you use separate BE's to begin with?

Have you considered using a SharePoint table simply to assign IDs for new customers? That way, everyone can add the common customer fields to the SharePoint list and use that for reference. The downside of this of course is that you loose the ability to enforce RI and will be forced to handle it manually when entering an order.

You can also figure out about how many new customers are defined each day and plan ahead. In the customer table, pre-generate dummy ID's. Say you generate 10 dummies for each of 5 stores. When a new customer comes to store B, pick the first unissued ID and fill in the info. Include the name and address and phone, etc as well as the date issued. When the databases are sync'd later, the sync process will see that one of storeB's dummy ID's has been used and so generate a new one. The problem with this is that you have dummy records in your customer table. The solution is in your queries that select customers, ignore any rows that don't have a StartDT filled in so you don't use these unassigned IDs for anything like giving management an accurate count of Customers.

Another option, which has probably already been suggested (I admit to skipping to the end once I read the bad article reference) is to generate a custom sequence number with part of the code identifying the store that issued the ID.

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