Autonumber Alternatives...

[Bubbis Thedog]

Hi there, folks!

I was wondering what alternatives there are to Autonumber in Access. I found a tidbit of code while Googling (Searched: "alternatives to autonumber access"), but I'm not sure exactly how to implement it.

Any advice, anyone?

Thanks as always!

 

[Norie]

Vinny

What do you mean?

 

[Bubbis Thedog]

Hi, norie.

Well, I read this, http://www.applecore99.com/tbl/tbl012.asp , when I was looking for a way to generate unique primary key numbers automatically, but not be restricted to using the AutoNumber data type. In the past, for me at least, AutoNumber has been a hassle to deal with. I'd like to have more control how numbers are generated.

I know that you can modify AutoNumbering using Paste Append, but, once again, it's a hassle, and it's proved unstable for me in the past. I'm just curious if there's some other route can be taken to generate unique primary keys automatically.

Thanks, norie!

 

[Joe4]

I personally hate using AutoNumber. I have many databases that use multiple fields as the Primary Keys, or I create my own using a combination of other fields i.e. I take the first 3 letters of Company Name field, and then our 5 digit Plan Code field to create a unique Primary key. If I already have one that matches, a simply tack a counter on to the end of it.

 

[Bubbis Thedog]

Right, jmiskey. That's a great suggestion and I've done that in the past, too, with tables containing at least several columns. But the tables that I'm wanting to do this for now are two- or three-field static tables (including the primary keys). Thus, I can't use that approach here.

I mean, if I set a field as a primary key, I can't enter duplicates; that's all fine and good. And i can, as you know, force the entries to fall within certain critieria. But the kicker is that the values are not generated automatically. I'd like to have code that tells the tables that, once a new record has started being created (for instance, a value is entered into column 2 of two columns), assign a primary key value for column 1 that is 1 greater than the value above it. This way, I'd eliminate the AutoNumber thing, and always have sequentially-numbered keys.

If they weren't static tables, I'd probably settle for Autonumber, to be honest, because I don't care about sequential values in dynamic tables. But in static ones: yeah, I'd like an alternative.

Thanks so much for the input!

 

[Norie]

What's wrong with using AutoNumber as a primary key?

In my opinion a primary key should not really represent any data, it should only be used to link tables.

Also it should be hidden from the user.

 

[Joe4]

What's wrong with using AutoNumber as a primary key?

In my opinion a primary key should not really represent any data, it should only be used to link tables.

I use my primary keys to link between tables, and I like my primary keys to be at indicative of the data they hold.

Many of my tables and relationships are complex, and using an AutoNumber instead of something indicative makes trouble shooting much harder for me.

I have heard strong arguments on both sides of the issue. I guess it really comes down to personal preference.

 

[Norie]

I guess it really comes down to personal preference.

Yes

And also if you are creating a database for yourself or someone else.