Custom Autonumber function

[dymondjack]

Second (third, fourth) opinions?

When I first started learning about access and VBA programming, I came
across a few threads regarding inconsistancies with the Autonumber datatype.
Here is some information on it provided by Allen Browne.

http://allenbrowne.com/ser-40.html

One would not expect to have to worry about failures with access's
autonumber field, but this type of problem could be detrimental should it
arise.

I have since come up with a workaround that involves a table (tblSys, again
adapted from Allen: http://allenbrowne.com/ser-40.html (Section 1)) to store
the last used number (Variable = "LastANum_" & tablename).

I set my PK fields to Long Integer rather than Autonumber, and I have a
public function fAutoNum(TableName) that looks up the last used number for
that particular table, and finally, edits the Value of the tblSys variable by
an increment of 1. The function looks something like this:

Public Function fAutoNum(TableName As String) As Long
Dim lID As Long
lID = 1 + Clng(Elookup("Value", _
"tblSys", _
"Variable = 'LastANum_" & TableName & "'"))
fSysTableEdit("Variable = 'LastANum_" & TableName & "'", Str(lID))
End Function

(fSysTableEdit updates a Value for the specified Variable in tblSys)

So, my function returns the next number for the table, and updates the value
by an increment of 1.

So far this has worked out very well. ID values on a form are easily set by
calling the function on the BeforeUpdate event of a new record, setting the
value in code when adding a record using the Recordset object is just as
easy, and calling the function from within an SQL string has not been a big
deal either. So as long as I follow the 2nd commandment (Thou shalt never
allow thy users to see or edit tables directly, but only through forms and
thou shalt abhor the use of "Lookup Fields" which art the creation of the
Evil One.) I think this should work out fine.

Aside from a slight performace drag when processing large amounts of
records, does anyone see any reason NOT to do this? I've never heard of it
being done before, and so far I've processed approx. 1500 records using this
method.

Has anyone ever tried this or can think of any major downfalls to this in
the longrun? The only issue I can see is with replication databases, but as
near as I can tell that would be an issue using the default Autonumber field
setting as well.

Any thoughts would be great, I'd hate to have this one bite me later on in
case I'm missing something.

Thanks!

--
Jack Leach
www.tristatemachine.com

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away." - Antoine De Saint
Exupery

 

[dymondjack]

Correction:

I have since come up with a workaround that involves a table (tblSys, again
adapted from Allen: http://allenbrowne.com/ser-40.html (Section 1)) to store
the last used number (Variable = "LastANum_" & tablename).

Link should be:

http://allenbrowne.com/ser-18.html

--
Jack Leach
www.tristatemachine.com

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away." - Antoine De Saint
Exupery

 

[Allen Browne]

The downfalls have to do with the fact that Access is a multi-user database,
e.g.:

a) If two users try to save new records at exactly same time, they could be
given the same number. Using Form_BeforeUpdate (the last possible moment
before the save) reduces the likelihood, but if you have several users
hammering away adding new records, it will happen.

b) Using a single table (tblSys) where you record the last used autonumber
value for your tables means that everyone is constantly updating this table.
You will hit concurrency problems on this table much more often than on the
other tables where you use your custom autonumber.

To be bombproof, you would need to:
- Use a separate table to hold the current autonumber value for each table
where you do this (to avoid (b) above.)

- Write your function so that it will:
i Exclusively open the table that holds the autonumber value in a
transaction, and hold it locked.
ii Read and increment the value in your code.
iii Assign the incremented number to your main table.
iv Save the record in the main table.
v Save the new value in the autonumber value table.
vi Release the lock.
vii Add multi-user error handling to cope with clashes.

The error handling might contain code that performs a limited number of
retries with random pauses between them, and rolls things back if the save
cannot occur.

Since the AutoNumber already handles this kind of thing, that's the reason
you don't see people bothering to write this kind of thing very often.

 

[dymondjack]

I see. I suppose those that invented the wheel the first time had things
pretty well rounded out, so to speak.

Thanks for the insight.
--
Jack Leach
www.tristatemachine.com

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away." - Antoine De Saint
Exupery

 

[Dorian]

I've used Access heavily for over 10 years and never had the autonumber
function fail. Just don't rely on it always giving numbers in sequence and
expect to get gaps in the sequence when users hit 'escape'. The only time I'd
use a custom function is when the user insists on some non-numeric
identifier. Even then, I'd still keep the autonumber as my primary key.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Bob Larson]

A big thumbs up for that comment Dorian (especially the part about still
using the autonumber as your primary key)!

--

Thanks,

Bob Larson
Access MVP

Free Access Tutorials and Resources: http://www.btabdevelopment.com

 

[dymondjack]

Thanks for the replies guys, I really appreciate it. I don't really have a
lot of "long term" experience when it comes to data structures over periods
of years. Most of what I have done involves relatively small dbs to perform
some task or another, and I'm at a point now where I'm trying to consolidate
and integrate all of these into one large system to handle every piece of
information coming into the workplace.

Within my first few months with access and VBA, coming across a few threads
and seeing some literature about the Autonumber not working correctly (I
worry more about duplicates, I don't really care about ID's being sequential
or getting more 'off the wall' values). That, and a person that I was
refering to help in the beginning made it a point to strongly advise to
never, NEVER use an autonumber function as an ID (why, I have no idea, though
he has done almost exactly what I'm am working at now, and *apparently* has
done a good job with it, as many of his users are friends of mine and say the
application works great).

Anyway, between those two statements, I think I came into this with an
impression of autonumbers breaking and leaving me with a serious mess (though
I could never understand why this person is seemingly contemptous of
autonumbers... I had given it much thought after he advised it, but could
only see it leading to data structures that I am not comfortable with and
lots of unnecessary (IMO) work to accomplish the same task. He even went so
far as to say don't use numberic IDs, but rather multiple PKs for
relationships, which I also had a hard time accepting).

Allen has certainly brought up so good points on why a custom autonumber
function probably isn't the best idea, and your testimony(ies) on relying on
autonumbers over extended periods with no issues is very encouraging when it
comes to making my final decision on what do to with this.

I suspect I will ditch this custom auotnumber function. There seems to be
no advantage whatsoever, and autonumbers themselves apparently present no
long term issues assuming I have well normalized data.

Again, thanks for the input.


--
Jack Leach
www.tristatemachine.com

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away." - Antoine De Saint
Exupery