Autonumber vs. DMax 3

[bangsmic]

I have a project where each new record needs a message number automatically generated , increments by one, and does not allow duplicates. I favor using the DMax method while others advocate the autonumber method because that is what they have always used.

How can I best argue the point that DMax is the best method to accomplish this? What are the bad points of using autonumber as a visible, unique, reference # for a record (EX: Work order #, Invoice #, Message #)?

 

[Lightning]

The main problem with using an autonumber field is that if the user cancels out of the record without saving the new record, there is no rollback of the autonumber. The cancelled number simply doesn't exist in that recordset. If incremental increases are NECESSARY, then autonumber is not the answer.

HTH
Lightning

 

[gusbrunston]

[tt]
Hi:

I'm still trying to get out from under the tyranny of Autonumber. I began to use Autonumber in my tables before I learned better; by then it was almost too late. The benefits of using Dmax may not be apparent to users, but it will certainly make the programmer's life easier.

Not that I feel very strongly about it or anything!

Cheers, Gus Brunston An old PICKer, using Access2000.

 

[gusbrunston]

[tt]
I know, you asked for arguments.

The best answer to "What's bad about Autonumber?" is that it is almost impossible to find anything that's good about Autonumber. It lays there sopping up air, in the unlikely event that someone will have to refer to it to distinguish it from some other otherwise duplicate record.

Anything Autonumber can do, DMax can do better.

DMax can identify duplicate records as well as AN.

DMax is easily manipulated; AN is not.

DMax is easily reset; AN is not.

DMax can increment by 2, or 3, or more, instead of 1; as far as I know, AN cannot.

DMax can decrement; again, as far as I know, AN cannot.

DMax is an obedient servant; AN is a hard master.

etc.

Cheers again,

Gus Brunston An old PICKer, using Access2000.

 

[MichaelRed]

Hmmmmmmmmmmmm -

DMAX CANNOT guarntee uniqueness. Which is the "Saving Grace" of AutoNumber. Although there are problems using autonumber in a multiuser environment.

As a 'compromise' between these, I wrote a routine to generate a customized and unique value as a 'pseudo' autonumber. I also posted the 'generic' version as faq700-184 -which, I am told is not for the faint of heart, although it seems straighforward to me, there are numerous small changes which are necessary to fit it into a specific app.

As for the 'down side' of DMax, you really should understand that is is nothing but a poor (programmers) avoidance of writting a simple query. (Select Max([MyField]) as TheMax From [MyTbl] ?? So why invoke the wrapper procedure to build the query string from information you need to supply anyway? After all, once the query is 'built', little olde Ms. A. will insist on 'compiling' it to check for syntax errors EVERY TIME it is used - so you really are just introducing delays in processing of EVERTHING?

Neither DMax or AutoNumber can - by themselves provide the functionality requested here, I am not sure that I would agree to implement the function in any real app. Even if the monotomically incrementing value is generated, most dbs will eventually permit "deletions" - which would immediatly destroy the nice little uniformity of incrementing values. So, one either needs to renumber the entire set (and then need to deal with any and all relationships built on the value), or accept gaps in the sequence. I -for one- long away and far ago accepted (actually ran to embrace) the gap approach and have never looked back on the decision (at least not in the sense of questioning it).

Simplistically viewed, the process of generating any "AutoNumber" type is to 'guarntee' the uniqueness of the value so it can be used as a PRIMARY KEY.

DMax fails in the multiuser environment, as previously stated.

AutoNumber is often rejected as many 'programmers' feel that the 'waste' of the few (6?) bytes per record to generate a value which no USER needs is poor practice.

(although many of these SAME programmers do not hesitate to accept the default field sizes for other data types, (String 255 ring a bell here?) or to deliberatly size fields 'generously' to accomodate the VERY occassional XXX size value (Ever seen a FIRST Name field as String 35? - EVER Seen such a thing actually NEED all {35}?)

My 'compromise' suggest that you may want some 'useable value' and STILL want a PRIMARY KEY. The ONLY answer to this is to 'roll your own'.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[gusbrunston]

Wow!

As usual, I am truly grateful for Michael's erudite comments. MR, this is one I'll print and file, for future study.

Thanks,

Gus Gus Brunston An old PICKer, using Access2000.

 

[gusbrunston]

Michael:

I was early on told that even if the size of a field in a table was large, if there was no data then no storage space was used. Is that right?

Thanks. Gus Brunston An old PICKer, using Access2000.

 

[MichaelRed]

While I'm NOT sure, that is not my understanding. MS 'discussion' of db size vs field size suggests that the storage is 'reserved' regardless of its use. Further, If space is ONLY allocated as it is used, why would 'they' (the great neaboulous MS-er's) take ANY time/space discussing setting field sizes? I'm SURE this is DISCUSSED in several articles, so there must be some purpose?

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[MichaelRed]

Weeellllllllllll -

I generated a REALLY kludgy test on the used / allocated question. At least at FIRST glance, if the original record addition does not populate a field, the FIELd in the RECORD is not allocated. The "tracking" information does appear to be included (where the discussions refer to additional bytes for field definition). I will do some more -re updating fields and only using a portion of the allocated space, deleting field contents ... -but not today.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[bangsmic]

Interesting responses. How about a compromise?

Use Autonumber as a primary key, but not visible on the form.

Use Dmax as the reference # (work order #, Invoice #, Message #, etc.) which is visible on the form and applicable reports for user reference.

 

[MichaelRed]

Hmmmmmmmmmmmmmmm - Compromise == Camel (horse built by committee)? Possibly the worst? How will the DMax help if [reference #] is not unique? Which of the (possibly several) records (Values) will this returm? How will KNOW wheather it is what you're really after? Not to mention the PREVIOUS commentary on DMax (actually [most | all] of the "Domain Aggregate" functions operate in the same manner - just a wrapper which generates an SQL string which is passed to the query parser ... all to just slow down the application).

And what use is the Auto thinggy if it is not being used to guarntee the uniqueness of what you are getting? Why HAVE it unless you USE it?

If your "LookUp" is guarnteed to be unique, you do not need the Auto thinnnnnnnngy, then the lookup (HOPEFULLY from a stored query) works well as the RETRIEVAL mechanisim. It still needs some process which can generate the next value AND GUARNTEE it's uniqueness. DMax can't - won't, because it simply does not lock access to the field.

As 'they' say in the trade, 'boys will be boys' (and conversly do what they want) so trapse right on into that briar patch. As long as it's not MY fur them briars are pullin at I don't rightly care!

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[bangsmic]

does this really come down to whether or not the program is being used in a multi-user environment? All I want is number, which automatically increments by one when a new record is created, and can't be a duplicate of a previous. Either method will accomplish this as far as i can tell (short sighted). What happens down the road and the method i choose in the beginning it what concerns me.

BTW, some of your commentary is over my head so i am trying to keep up.

 

[JeremyNYC]

Michael and I have disagreed about whether or not autonumbers leave you vulnerable to dupes in a multiuser environment. I've been doing this for a long time and feel strongly that they do not. But I'll leave that alone for now.

But he is right that DMax is not at all a suitable replacement for an autonumber. I haven't read through his code, though he knows what he's doing and I'm sure his code reliably generates unique, sequential IDs. If you're not going to use an autonumber, you really should use something more robust than DMax, like the code Michael's written.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[bangsmic]

Thanks to all for the insight.