Random ID in Access

J.B · 14 Jul 2008 at 13:09

Hey all, my Access skills arent great but I know the basics. What Im looking at doing have an AutoNumber as an ID field, but needs to randomly generate. In the design view for the table I have set the field to AutoNumber and changed the new values to random. Trouble is it is generating numbers 10 digits long, is there anyway I can limit it to only generate 6 digit IDs?

Dj_Jestar · 14 Jul 2008 at 15:16

Why randomly? AutoNumber (i.e. sequence) is much more sane.

J.B · 14 Jul 2008 at 17:18

I didnt write the brief! Its something to do with ananimity of the users, the users cant guess another users ID

RockLobster · 14 Jul 2008 at 17:31

Why not stick with the 10 digits Access gives you and just use the 6 first digits?

Moredhel · 14 Jul 2008 at 18:22

Use auto-incremented IDs and use other user data in-site to reference users?

A hash of the ID + email or something.

J.B · 14 Jul 2008 at 20:32

RockLobster said:
Why not stick with the 10 digits Access gives you and just use the 6 first digits?

To validate the login the user will need to type in the full ID.

Looks like I'll have to stick with it, shame though, some our even coming as negative numbers!

GravyMonster · 14 Jul 2008 at 20:51

Moredhel said:
Use auto-incremented IDs and use other user data in-site to reference users?

A hash of the ID + email or something.

This is how I'd do it. You'll need an incremental key to keep within the standard database normalisation rules (here).

As Moredhel says, just have a column with an incremental ID for internal use, then another column with a calculated & hashed random ID which can be used for users logging in.

Dj_Jestar · 14 Jul 2008 at 23:48

or let the users pick their own username..