We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Access 2007: Start an AutoID field with a number other than one

Bevos
Bevos asked
on
Medium Priority
566 Views
Last Modified: 2012-05-11
Hello, I have a database which has several AutoID fields which are used as primary keys.  I would like to control the numbering of these by making the AutoID start a specific number (ie. start at 10000 rather than 1) can this be done, and if so how?

Thanks so much,
Bevo
Comment
Watch Question

Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Wow, thanks for such a quick response rumrigger! This looks like it would work, but I am a bit confused as to where I would input this information.  Could you help me with that?

Thanks,
Bevo
Chris MangusDatabase Administrator
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Hi cmangus, I know this has no meaning other than the relationships defined by it, but I need this to (I know this is awful database practice too) give different users a copy of a small database so that the data they enter can be mered.  That is to say the autonumber fields start at 10000 for one user, 20000 for another etc... the overlap will not be a problem as no user has more than 100 records.  This will allow me to merge the databases and keep the relationships.  I know this is an AWFUL way to do this, but I don't have the knowledge to make an Access front-end and these people can use a shared network to do this small project.
Chris MangusDatabase Administrator
CERTIFIED EXPERT

Commented:
While no user can have more than 100 records, if they are entering and deleting over time you may still hit a limit.

With all the limitations in mind that you outlined I'd use VBA to generate your ID numbers, as needed and I'd also use a larger gap, perhaps 100000, 200000, etc.

I've seen projects like these scale out beyond what we ever think they will become.  With that said, you might want to find the expertise to help design a split design to avoid future problems.

Author

Commented:
Could you please briefly explain split design?
Bevos, I think that question is likely to generate a larger participation from other experts on the forum and you will benefit from posting a new question.

Whilst I agree with cmangus and he does make a valid point, absent any further budget to get the professional achitectural design you need for a split distribution, what you propose is a valid subsitute, I too have used this.

It would be a very easy thing for you to interogate the table in question (in the Database Open Event) which emails you an alert if the user is approaching their individual 10,000 row ID limit. It would likely take many insert/deletes to trigger this threshold, especially if the data population is a near "one off" occurance. To safeguard against this cmangus's suggestion to increase this to 100,000 is entirely appropriate.

regards
Dave
PS: I use the gender "he" because I am a male, its one of those psychosomatic things, or so the Psych tells me, LOL.

I do not use it out of some sex discrimantion process, so apologies if you are a female cmangus.

Author

Commented:
I'm going to close this question now. Thank you both for your excellent suggestions and I hope we can talk more in the future :)

Best,
Bevo
Chris MangusDatabase Administrator
CERTIFIED EXPERT

Commented:
I am male...and no offense was noted  :)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.