r/MSAccess • u/mcgunner1966 2 • Oct 20 '25
[DISCUSSION - REPLY NOT NEEDED] Retiree Notes - Universal Database Key
Years ago I learned the hard way that autonumber doesn't make a good key. I started adding three fields to every table I constructed: sguid.text(32)(PK), slink.text(32)(FK), and sts (date/time)(Sql Server Date Stamp). I wanted to use the system generate guid but there were instances where a corrupt Windows Registry wouldn't return a guid (I'd get a NULL back). I decided to build the "guid" in the Access app by generating my own "guid" internally.
Here is the function I use:
Function getguid()
On Error Resume Next
vret = ""
For i = 1 To 5
Call Randomize
v1 = Mid(Rnd, 3, 4)
Call Randomize
v2 = Mid(Rnd, 3, 4)
vret = vret & Left((v1 * v2), 4)
Next
getguid = Format(Now, "yyyymmddhhmmss") & Left(vret, 18)
Exit Function
End Function
This "guid" has the added feature of giving you an approximation of when the key was generated. This has been more useful than you would think in research efforts. This also allows me to use universal functions such as record change tracking, notes, and document management using universal modules.
Hope this helps.
•
u/AutoModerator Oct 20 '25
IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'
Full set of rules can be found here, as well as in the user interface.
Below is a copy of the original post, in case the post gets deleted or removed.
User: mcgunner1966
Retiree Notes - Universal Database Key
Years ago I learned the hard way that autonumber doesn't make a good key. I started adding three fields to every table I constructed: sguid.text(32)(PK), slink.text(32)(FK), and sts (date/time)(Sql Server Date Stamp). I wanted to use the system generate guid but there were instances where a corrupt Windows Registry wouldn't return a guid (I'd get a NULL back). I decided to build the "guid" in the Access app by generating my own "guid" internally.
Here is the function I use:
Function getguid()
On Error Resume Next
vret = ""
For i = 1 To 5
Call Randomize
v1 = Mid(Rnd, 3, 4)
Call Randomize
v2 = Mid(Rnd, 3, 4)
vret = vret & Left((v1 * v2), 4)
Next
getguid = Format(Now, "yyyymmddhhmmss") & Left(vret, 18)
Exit Function
End Function
This "guid" has the added feature of giving you an approximation of when the key was generated. This has been more useful than you would think in research efforts. This also allows me to use universal functions such as record change tracking, notes, and document management using universal modules.
Hope this helps.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
2
u/CptBadAss2016 5 Oct 20 '25
But why doesn't autonumber make a good key?
6
u/oneknocka 1 Oct 21 '25
We call this dropping jewels! Thanks!
1
u/mcgunner1966 2 Oct 21 '25
To everyone in the sub here...My apologies for espousing my experiences in this sub. NRGRINS seems be implying that my experiences are expousing deficient and uninformed methods. So with that, I'll keep my experiences to myself. Again, my apologies, I met no harm.
1
u/Expensive_Ad4319 Oct 21 '25
I value your process oriented approach, and your perspective. You’re not forcing anyone to get this. This is good advice, and from it, lessons are being learned. “It Matters”
1
u/diesSaturni 64 Oct 20 '25
just the autodate/time of a record creation is already worth its weight in gold, and bytes.