Custom auto number ids possible?

[twgonder]

I hate to ask, but I don't see any way with native Access to customize the auto numbers for a primary key. Why you ask?


Well, fifty years ago it was quite common to write our own code for everything, not just auto numbers, and we wrote code to do the autonumbers, however we didn't use them quite as much as is common in Access. Usually, the Employee id or the Customer id was the primary key used as a foreign key.

One of the things we did to support multiple BEs that needed to synchronize was to add a three digit number to the front of every id in application files that might need synchronization. This meant most everything except local control files/tables that remained static. This thee digit number was specific to a BE, so that when rolling data up and down, you knew which BE created and needed the synched table records. It eliminated duplicates across the entire network. Much easier than having a huge globally universal number, unless you go outside your controlled network, but that has it's own unique problems.

If Access doesn't have a way to tweak autonumbers to reserve the first n digits and load a variable there, then I guess a table could handle that with some code in each place that can create a record (like we used to do). It's that last part with queries that has me concerned. I think this was one of the main reasons I decided Access wasn't yet fit for the "real-world" I worked in some thirty years ago.

Any other ideas on how to handle this? (I have yet to test if composite ids work between tables when forming relationships, but that seems a bit complicated by comparison.)

[Micron]

I decided Access wasn't yet fit for the "real-world"

Really? So the rest of us live where? Time to move on from 50 years ago I'd say. Maybe this will help you understand where we're coming from wrt autonumbers

http://access.mvps.org/access/general/gen0025.htm

Maybe someone else has a bookmark for what is an excellent treatise of the subject (title is something like Autonumbers: what they are and are not). I can't find it. An's are a way to uniquely id a record - nothing more and typically should not be comprised of meaningful data. If you want additional tags/identifiers, add fields and look for ABC123 in your queries in that/those field(s) to figure out which pc added a record as an example.

[twgonder]

@micron Well, I understand quite well the question and answer in the link you gave. It doesn't address the situation I described.
So, how have you or others addressed the issue of a unique id across a network without resorting to a Replication id, which everyone I've talked to recommends avoiding like the plague? The GUID is a complicated answer to a simple problem, as I said, a problem figured out decades ago with a simple Einstein approved solution. What, move on from a four byte solution to one that requires 16? I'm not sure what you are trying to say with the ABC123 example. Are you implying a composite key like I mentioned?

[twgonder]

Oh, and here's one with a similar situation: https://www.accessforums.net/showthr...616#post453616
I guess his problem has to pull it's head out too and get with the times?
I notice that it's been two years without a solution, Hmmm

[CJ_London]

Think it is time you stopped haranguing Access/VBA/Windows for not being what you want. You want to create your own cross BE PK's? Access provides a way. You don't have to use GUIDS, you can build your own but GUIDS were design to use with Access replication and are frequently used in enterprise scaled solutions, they just don't make good primary keys in Access and I suspect those enterprise solutions either, if you are not happy with autonumbers don't use them - populate a numeric or text field exactly the way you want, use your 'simple Einstein approved solution' (whatever that may be), perhaps google 'dmax+1' for some element of automation. If you are not happy with that way, use Visual Studio or similar - that will take you back to the good old days of doing everything, and I mean everything, from scratch.

Your constant whingeing, sniping and criticism on this and other forums is becoming tiresome (to me and a few others at least). Access is not perfect, no development environment ever is. But it is perfectly fine for 99.9% of the developers who do use it and are happy to work within the limitations as a result. Those who aren't, don't use it.

Just seen your post#4 - see no reason why you could not use 'office:date:milliseconds' (is that your Eienstein approved solution?) as PK in principle if you feel that is the right way to go. Would that be a fully numeric value? say 5000 offices so 4 digits plus a date which is 5 digits (hmm, better make it 6 to make sure it will still be valid for say the next 2500 years) plus another 8 digits for the potential number of milliseconds in a day - so an 18 digit number. And strangely still no guarantee of uniqueness. since potentially two records could be inserted at exactly the same time. Very unlikely, but possible.

[twgonder]

@CJ_London. Sorry you think I am whingeing, whatever that is. I'm looking for solutions to common problems. It's a long story that I'll keep short, but my boss, forty years ago (and he was then forty years older than me), was the son of one of the chief software engineers at IBM. We had a client that was buying another truck dealership with one of our systems (a first for two BE working in tandem for us), and I, all the age of 25, was all alight with the solution, down to the second, which was good enough back then, before the hardware vendor started offering milliseconds. Phil politely listened to my solution and then told me to look in a particular file, record and field. And there sat an interesting number that I had seen in most of the files/records (the word for tables). "Oh, now I get it." The DOD used the same scheme for document sharing before the Internet grew into what it was to become. Albert Einstein worked with my boss's father; my boss claimed it was an idea of the old physicist. It was a lot to give up an extra byte in every record back then, however the solution worked great until the day there were more than 255 computers in the world of the DOD that needed to share documents.

Anyways, thanks for confirming that the idea might work in Access, I'm not sure if there is a function to get milliseconds. So, what do you do if two people put a code of "A" in a form as the primary key that doesn't allow duplicates for a table that doesn't use autonumber? Does it matter when they do it if there is a clash? Have you had any trouble with queries that might clash when updating to a table that doesn't use autonumbers?

Sixteen byte keys aren't all that uncommon as a primary key in millions of working systems, so I do have a little trouble understanding why replication ids bring Access to its knees as some claim. But, they were the pioneers that took the arrows, so I'll take their word for it. Unlike most developers, I'm taking the time to analyze the BE problems before I rush into designing applications for a single BE environment, like most with Access appear to have done.

As to the other forum, I asked a question a bit larger than the one here. And what did I get? Some crap about gray wall B.S. about a solution that's been around for almost 100 years in computing. I'm supposed to get with the M$ program, which I may be able to do, after overcoming lots of simple design flaws in Access. (I'm sure you've had to work around your fair share.) Hence, I'm asking now for ideas. I'm sorry that bothers you. I know I seem to have come out of the woodwork with a lot of questions. I do appreciate the help you've so generously offered so far.

[twgonder]

And for those that think I'm just here complaining and not working hard enough to find a solution for my own basic design structure, for every question I ask I've spent time in testing and researching. Often they are ideas of others that have, obviously never been implemented or tested. Or they wouldn't pipe up with a useless suggestion, that I could guess at too. I'm not complaining, but I too can think up dozens possible simple solutions to a not so complex problem that ultimately won't work. And often I have.

For example, here is a simple composite key relationship that I can't figure out what Access is doing. There should be one link line, and the relationship build should offer up the indexes and not just the fields, but I can't see how to get it to do that. I can't show the indexes for the two files because Access closes the index forms when the relationship form is opened. So, I'll add a second screen shot of one table's indexes to help explain. In this test, I'm trying to get the foreign key of AUpid to relate to the primary key of TblP. No luck finding the way yet, it is a no brainer in most dbs.

Attachment 48496

[Micron]

Or they wouldn't pipe up with a useless suggestion, that I could guess at too.

Therein lies part of my/our problem: your tone, for lack of a better word, is off-putting. We volunteer our time freely and I for one will stop trying to help when I read those sort of comments. I could go on but will end by saying it seems you did not grasp the point of the autonumber link (post 2?). Since autonumber cannot be anything but unique, what would be the point of a composite index or field that involves autonumbers? A two-field composite index will allow
A,B
A,C
A,D but not A,C again. If Field1 is autonumber, unlike the field containing multiple records with value A in that example, the autonumber can never repeat. Thus there's no point to such an index, notwithstanding that the advice to not use autonumbers in meaningful data seems to have not been taken as well.

[twgonder]

Therein lies part of my/our problem: your tone, for lack of a better word, is off-putting. We volunteer our time freely and I for one will stop trying to help when I read those sort of comments. I could go on but will end by saying it seems you did not grasp the point of the autonumber link (post 2?)...., the autonumber can never repeat. Thus there's no point to such an index, notwithstanding that the advice to not use autonumbers in meaningful data seems to have not been taken as well.

I'm sorry that by speaking truth, I offended you. If you understood me, and maybe I didn't make myself clear, BE1 and BE2 could and most likely would have a customer with autonumber 1. I'm trying to get an "autonumber" that is unique across the network of computers. I think this was made clear in the first sentence of the second paragraph in my original post (and explained in the rest of the paragraph). As I tried to show with the screen shots, the suggestion by some to create a composite key doesn't seem to work in Access for foreign ids, even though Access allows you to create a composite PK.

I know some are trying to help. But others just seem to want to say, and I paraphrase, "You old timer need to get with the program and do it the new way." As if anything is really new in computing or history. Either they don't offer a solution to the problem (so who is the one uselessly complaining?) or they suggest some untested idea, or solution to a whole different problem (again that might be my communication fault, often why I try to be explicit). I've done my best to ignore the lambasting and return to the problem at hand. Other times, someone says something, and I wonder, could I do something close to that idea that would work? Instead of wasting hours testing something that gets roadblocked by Access, it's nice to hear from someone who has been there before with success.

That all said, many have offered great ideas or solutions to other questions, and I have expressed my gratitude to those (including yourself I believe).

[Minty]

I'm afraid I tend to agree with some of the comments about the negativity of your tone.

You seem slightly obsessed with use of memory, size of storage etc. 40 years ago, these things were very important.
These days they aren't. Plain and simple, huge storage is very cheap, reliable and readily available. 4 bytes or 16 - who cares? Any 5 year old laptop, phone or tablet has Gb's of storage to waste.

You also seem pretty hell bent on using Access in a business model that is WAN based, but want to base it on LAN infrastructure and offline techniques.
Nobody does that these days, because most business models rely on having a decent internet connection.
An Access BE is not for you in the WAN world, use an SQL Azure Cloud based backend and remove the need to synchronise disparate backends.
Access works very well with an Azure based BE file, if you use the latest drivers which accommodate WAN connection issues that used to make it a non-starter.

Whilst 40 year old programming practices have their place, you need to allow for the fact that there is a reason a mouse or touch screen is popular.
It's called progress, even if old gits like me (and it appears you) don't always see it as progress.

Access isn't even close to perfect, and has been stuck in Microsoft's broom cupboard as far as real development is concerned for at least 15 years, however it is still the best RAD tool for a relational database front end I have seen in the last 25 years.
It is not an Enterprise Software Development tool/framework, and never will be, but in the right hands it can do a very good job of producing things that imitate it.

TLR - Move into the current world please.

[CJ_London]

I understand perfectly well what you are trying to achieve, and I don't see it as 'old time stuff'

You are complaining that you can't get an autonumber to do something it is not designed for. It is intended to automatically uniquely identify a record in a single table in a single database, end of, beyond that it has no meaning. If you want one to uniquely identify a record in a single table across multiple backends, you build your own. Check out sql server, my sql etc they probably do have something that does the equivalent of office:date:milliseconds'

Sixteen byte keys aren't all that uncommon as a primary key in millions of working systems, so I do have a little trouble understanding why replication ids bring Access to its knees as some claim.

but those systems are not ACE. The reason they are not so good for ACE is not so much the PK, but the FK. In ACE the idea of the GUID is to uniquely identify a record anywhere, regardless of table and database. You want something in between.

Sixteen bytes may be common in some db's but that does impact index performance, certainly for ACE. Simplistically, a standard autonumber is 4 bytes so 16 bytes will be 4 times slower. How this pans out in real life depends on how you app is designed to work. If you want to use a GUID as a PK, go for it - the problem will be the FK's on the other end of the relationship. As a replication ID, it is doing the job it was designed for and does it pretty well. But you don't want replication, you want synchronisation.

I'm trying to get an "autonumber" that is unique across the network of computers. I think this was made clear in the first sentence of the second paragraph in my original post

Problem is you are often talking about international systems covering 100's of countries with millions of clients.

No luck finding the way yet, it is a no brainer in most dbs.

another snarky comment - you set the indexes for individual fields in table design. But from your screenshot, one table has cocd as text, in the other it's a number. Basic rule of relationships - fields must be of the same datatype.

As to why the options to set index properties is greyed out - no idea. Somewhere you will have fiddled with something or your access installation is not complete

[CJ_London]

I think I have mentioned on one of your other threads that I have developed apps where the 'remote' db is out in the field (literally) for utility company meter readers who could be in a field somewhere miles from anywhere to read a meter - back then no wifi, no mobile/cell hotspots. But all they were doing was data collection - when in the office, they downloaded the current meter info, out in the field they would enter the meter reading and perhaps some other basic info. They had the meter PK from their office download so all they needed was to use it as a FK. When back in the office it didn't matter that the PK for the remote record changed when uploaded to the main db - for continuity the remote PK was stored as an indexed, duplicates OK field together with a field identifying the meter reader.

Point is the flow direction of new data was all one way.

[twgonder]

@ CJ_London & @Minty

I apologize to those that don't like my tone. Maybe I am being a bit more cranky than usual. While getting back into Access, yet again, I've also been battling a new cell phone, one with the "newer" Android version than my 5-year-old one, which let me turn off the GPS so it didn't drain my battery in a day (and I can't find how to turn off GPS in the new one). There's all kinds of new features, that I don't need, but after a month, I still can't turn off the damn GPS! Aggghhhh (Sound familiar to some of my questions about Access?)

In any case, I have found the help from you two here to be invaluable. Thanks.

I'll just touch on a few topics in your posts (it's kinda hard to do when not online):

Yes, exactly, I'm wanting to do something between GUID and an autonumber. It's not such a foreign concept as I've tried to describe why and where it came from. It's frustrating for me, and I imagine other developers that M$ has a NIH mentality along with the fact that they tend to hire green "geniuses" to develop new-fangled solutions without first considering the history of what works and doesn't (like turning off the GPS in my phone).

As to M$, they've grown into an international company that supports languages and customs the world over. Like M$, I'm not interested in selling to a limited market such as the USA where most developers get by with just first and last name, one or two address lines and where the Internet is always on.

As to using some SQL server, Azure, etc. as a solution to synchronize. I admit I have no experience with those technologies. I suppose it's possible that that environment could handle conflicting autonumbers at a local BE by procuring an autonumber from the highest level BE, but I seriously doubt they are that capable. They may distribute load and offer redundancy, backups and other features, but I doubt they handled distribution of unique ids across a WAN. That's always been something that's handled at the local level in my experience.

Yes, collecting meter data on a small hand-held exists, I did similar uploads and downloads to clients for 25 years on an almost daily basis. What I've described in my posts, as BE synchronization is vastly more complex than just importing a file.

It does kind of piss me off when someone makes a comment about my question being antiquated for the new design and "RAD" model of Access. I understand the concept behind Access, accept that it has limitations, but as M$ drove all competitors out of the desktop and mini markets, to me they have an obligation fix bugs and enhance the product as the users reasonably request. That's the least they could do after crippling competition in the marketplace. I recall someone mentioned that the Access has been in the broom-closet for 15 years (did I remember that correctly--no Internet this second).

Some have suggested that I find a more appropriate tool to develop with. Read the immediate prior paragraph. The world wants to use Access, they want to integrate with Excel to create their graphs and with Word for their reports. What the user wants is outside of my control. The WAN/LAN environment that the typical user is in (half the Western Hemisphere) is outside of my control. My job is to find a solution that works at a reasonable price point.

Yes, computers have gotten faster and storage has become incredibly inexpensive. I've always designed with that in mind, but it doesn't mean I stupidly design with the hope that more speed or storage will fix it. I look at some things in Access, and I think, "If I had that forty years ago, the front-end I could have made!" Look at the silly command buttons for a form footer I described in one post. Being able to change a command button background color to red on hover if the user is about to do something stupid, but allowed, is to me, simply amazing.

I'm not sure which found my "bug" of using text/number for the sample of composite keys I showed. I was in the middle of putting together a simpler test of what wasn't working in a larger design. I hadn't fully reviewed it nor had I put in the data or tested the SQL. My bad for posting, and complaining prematurely. I wanted to get the post done before turning off the computer for the night. My bad.

To make matters a little more difficult for my test, Access had paused, closed down and restarted twice while doing this small test, leaving some things unsaved. It did this twice while creating this simple db (that has no code). It's done the same thing in my robust db, causing lots of data corruption. Aggghhh!



The good news is, despite the weird representation of the of the relationship, composite PK and foreign keys do seem to work. It's not a perfect model of the BE environment, but it does show that Access can indeed use a composite key properly for relationships, at least in this small test case.
I finished the design this morning, and to not bore anyone with lots of detail, the child records align with the parent records as they should.


And to test that Access wasn't just using one of the composite keys, I intentionally broke the parent record, and got a good result:



So, now were back to my original question. Does anybody have experience with either of the two or three solutions thus far presented for handling BE synchronization? What's worked best for you and what didn't and why? If you haven't done BE synchronization (or don't understand the issues or haven't read and understood my posts), then maybe (I won't say it because I'm trying to play nice)...

[Minty]

@twgonder thank you for a considered reply.

You phone - depending on your Android OS, drag down the top of the screen (notifications area) and look for the location icon. (Looks just like a Google Map Pin funnily enough)
Press that and then Turn Off Location slider. https://www.androidauthority.com/turn-off-gps-1040167/

Access - Your composite key (OfficeID+AutoNumber) should be sufficient to control a one way synchronisation. CJ has described briefly how to.
Two way synch get's a lot more interesting, and really relies on updating any master tables (ClientID's etc.) from the master only, or using a clever temporary local key that gets translated into a New Master once the synch process has occurred.
Alternatively you have a OfficeID + ClientID Key, and that gets manipulated in the main backend, the issue with that is duplication of clients for different offices, like I said it gets messy, without access to the master tables.

[twgonder]

@ Minty As to the phone, yes I can turn off location, but many of my apps need it. Android used to allow what 3 services determined location. One could disable GPS but use the other two (cel network and wifi network). That is gone as best as I can tell in my new phone. The GPS is the biggest battery pig of the three choices. Now it would seem all three are on all the time (unless all are turned off).

As to Access, yes, you are starting to get it. All solved 40 years ago. And not just by me, as my boss story showed. In another forum, I told about Ted, the truck driver, who later wrote the entire system for synchronization (up and down) in a month for systems that were 99% offline. Including for synching code changes that could move lines of code/down or insert/delete in the source code (programs were just big records, each code line a text field) modified by different programmers. Now it's up to me to determine if SQL/VBA is the tool to get it all done.