Max Wanadoo
max.wanadoo at gmail.com
Thu Jul 2 14:27:32 CDT 2009
I wouldn’t bother using dmax actually, I would open the table, lock it,
increment it, unlock it and move on.
I was just bouncing alternatives around in my previous posting. The only
right way is the one that give you the answer you need every single time.
I would put the lock flag in the same table as the sequencedescriptor and
its incrementing value.
Ie, tblSequences
Field:
SeqDesc
SeqStartDesc
SeqStartVal
SeqVal
IsLocked
Something along those lines. Code needs to ensure it gets a lock or times
out and tries again later. You also need a mechanism to unlock the
table/record in the event of crashes.
Max
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
Sent: 02 July 2009 20:16
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Custom autonumbering
Max, I have always had the same opinion about AutoNumber as you (only to use
it for surrogate keys). Drew has pointed out a solution though using two
tables which make an AutoNumber applicable for alternate ("natural" or
"meaningful") keys.
One thing I don't understand is why you too use two tables since your
solution is applying the DMAX function. Why not just use a plain Long
Integer and then on the form set DefaultValue to Dmax + 1, and supply an
event procedure to manage potential conflicts in a multiuser environment?
This was the approach I was suggesting in my first posting, and it seems a
lot simpler to me. Do you see any pitfalls in this way of doing it?
Asger
-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af Max Wanadoo
Sendt: 2. juli 2009 17:16
Til: 'Access Developers discussion and problem solving'
Emne: Re: [AccessD] Custom autonumbering
Asger, I have just read all your postings in this regard and it does appear
that you want your autonumber to be "meaningful". If that is the case,
don’t use autonumber. As many previous postings here will point out, the
whole of idea of an autonumber is that is has no meaning other than it is
unique within the database and can act as a pointer to the record.
IF you want a meaningful sequence in the way you describe with differing
start points, one way would be to have a table holding a field called the
"Start Sequence" as you mentioned before and then using Dmax to get the next
number. When a new sequence is started, add it to the table. If it is
already there, just increment it. Dont allow the same Start Sequnce to be
duplicated. If you want to ensure that no two people can get the same
number at the same time, have a lock-table which holds the tablename and
fieldname being accessed and don’t allow others to use it until it is freed
again. You can put a TIMER on when it locked and if not unlocked in x
seconds, then unlock it and release the TIMER.
Max
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
Sent: 02 July 2009 00:34
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Custom autonumbering
Hi Max
This possible would do for a unique autonumber. But it certainly won't do
for a natural, alternate, and customizable key-value (phew), which is what
I'm looking for.
Asger
-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af Max Wanadoo
Sendt: 2. juli 2009 01:05
Til: 'Access Developers discussion and problem solving'
Emne: Re: [AccessD] Custom autonumbering
Hi Asager,
I doubt if you will get more unique within the normal Access program then to
use:
Now() followed by a now() from (a reset of date - arbitrary number) added to
the original Now() and then add on the autonumber
What I am saying is, it is so easy to come up with a number that is "almost"
guaranteed to be unique..
Pick a Star in the galaxy. Any star..how many miles is that from
venus...that will do.
I am NOT being facetious. If you devise a system of generating a unique
number and you make your "system" unique enough, it will work..
Max
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
Sent: 01 July 2009 23:58
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Custom autonumbering
Actually I don't want to mimic the AutoNumber field type. I want a custom
autonumber which is 1) reliable sequential, and 2) customable. Your
two-table solution provides the first requirement and I will consider it for
future use. But sometimes I need to supply an easy way of creating a new
offset for the autonumber: e.g. using the current year as a leading number
the user should have the ability to change the number let's say 20098001 to
20100001, making this a new starting point for the sequential numbering
(normally this custom autonumber field should be locked on the form and only
editable providing a password).
I still find the DMAX-solution easier to manage for the second requirement.
But as said I appreciate your proposal for a reliable sequential numbering.
Asger
-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af Drew Wutka
Sendt: 1. juli 2009 23:52
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] Custom autonumbering
I've used Autonumbers as actual values (like Ticket numbers in a request
system) where the number doesn't have to truly be sequential. But in
the case you are talking about, it seems kind of odd to create a routine
that mimics the AutoNumber field type, instead of just using that field
type (in a separate table to keep it sequential). Hope it works out.
Drew
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
Sent: Saturday, June 27, 2009 7:02 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Custom autonumbering
Thanks Drew - your explanation corresponds my reading your first
posting.
I always use AutoNumber exclusively for surrogate primary keys, never
for
alternate natural keys like OrderNumber.
In my opinion AutoNumber (and the equivalent in SQL Server: Identity) is
designed for surrogate keys, not for natural keys.
You could use AutoNumber for natural keys, but then you are inviting
troubles. In Access pressing Esc on entering a new record will increment
the
AutoNumber, leaving gaps in the numbering. And depending on which
version of
Access you use, a Compact Database will reset the autonumber to the next
value after your records INcluding deleted trailing records, or it will
reset to the next record after your records EXcluding deleted trailing
records. Bottom line: You simply can't predict the value of an
AutoNumber
field.
This is what makes AutoNumber an excellent candidate for surrogate
primary
keys because this kind of keys is supposed to be meaningless.
You can ignore the special behaviour of AutoNumber if you use it as a
surrogate key like OrderID, but you certainly can't ignore it if you
want a
natural key value like OrderNumber, which is supposed to be meaningful.
Asger
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com