Can Use This Method for a PK - Customer Codes in Microsoft Access


	Back to Customer Codes Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Can Use This Method for a PK

Thomas Gonder

4 years ago

Sorry about the novel on "how to create custom Customer Codes" (3:30). If I could explain it easier I would.

Autonumbers are a great tool, which I used sparingly in my past designs (other dbs) as only part of a composite PK. I get the idea of using them in Access for simply relationships and not as meaningful data. I'm trying to design for multiple Back Ends. The classic solution of this was to have a three digit "computer code" (byte) prefix the auto number. But MS didn't allow for this. And as far as I can tell from testing, composite keys don't work for creating relationships.

Can we use the idea presented in this video to create our own custom autonumber? This would be VBA code that could pull the ComputerId (not the MS one, but one we maintain in a global control variable) and append date & milliseconds.

Depending on how dates are stored internally, we could maybe convert the string of concatenations
(CompId&date&milliseconds) back to a number to really confuse the poor user and save space. The vba function could do a quick Dlookup to make sure it's unique and then pass the id back as a default for the primary key field. In a weird circumstance, I guess it's remotely possible there could be a clash between two records with the same calculated key, but this really isn't any different than two people entering a non-autonumber record at any time (with the same Id) if it's indexed with no duplicates (except instead of entering a different value we just do the function again).

When synchronizing data up the BE chain, we would depend on the first three digits to define the BE source of the record. As to relationships, it's just like any other foreign key that isn't an Access system generated autonumber. With this, we have a unique number for Customers (and everything else) across the entire network of Back Ends.

Would this work? Can you think of why it wouldn't? What about queries that may add records to the table, would they pull the default used function value from the table design?

	Thomas Gonder OP @Reply 4 years ago
I'll answer my own question. It appears not. I spent two nights building a test and then function to create a custom id. Through thousands of tests duplicates were avoided. I built the function and placed it into the default value for what was to be the Primary Key...and Access says it doesn't allow user defined functions in the default value property. Big bummer.

	Richard Rost @Reply 4 years ago
Tom, use an Autonumber for the primary key. If you want to also have your own secondary key, that's fine. You can make it Indexed (No Duplicates) and use that for your purposes, while still allowing Access to maintain the AN PK for relationships and such.

	Richard Rost @Reply 4 years ago
See my various videos on AutoNumbers.

Thomas Gonder OP @Reply

4 years ago

For the past three weeks, I've been trying to understand how I'm going to support a chain of BE computers using Access. I think I've seen all if not most of the AutoNumbers videos.

Suppose I have a client with 300+ stores, that have to remain open and selling if their Internet goes down. They have to have a local copy of most tables, meaning 300+ backends. And those have to synch up later, up a chain of BEs. Other than saying, check out Sequel Server or Azure, nobody has been able to tell me how they've done this in the real world. I'm not sure having a secondary key is going to do the trick. I won't know until I learn more about SQL and SQL server and how I can use it for synching in this kind of environment. Got a seminar that tackles this problem? If so, I'm a buyer!

	Richard Rost @Reply 4 years ago
See this thread. You could use a Random AutoNumber instead of a sequential one. Or, if you really want to make sure the number is unique, use a GUID. I've actually been putting together a video on just this topic. Stay tuned.

Thomas Gonder OP @Reply

4 years ago

Some developers have told me to stay away from GUIDs, they claim it brings Access to it's knees. I need a ID that is unique to the network, so random won't do the trick. Here is the the key logic that I came up with that can use the currency field type. The next task was figuring out how to get it as a PK and FK and having it generated on the BE, and not on the FE.

Dim wrkId As Currency, BeCd as Byte
BeCd = 921 ' maximum number of BE Access computers
wrkId = (BeCd & Format(Int(CDbl(Date) - 44562), "00000") & Format(Int(Timer * 100), "0000000"))
WksSleepMs 8 ' must have mod_Au_Time, sleeps 8 milliseconds

It returns keys that look like this:
921002247107880
921002247107882
921002247107883
921002247107885
921002247107886
No dupes in a test of 1/2 million iterations (ran for a little more than an hour). It will work for over 271 years without modification. No exponential notation or decimals to deal with. Takes 8 bytes or 1/2 of GUID size.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Customer Codes.

Next Unseen

New Feature: Comment Live View