Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2011-04-27
11
Medium Priority
?
557 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
0
Comment
Question by:Bevos
  • 4
  • 4
  • 3
11 Comments
 
LVL 11

Accepted Solution

by:
Runrigger earned 1332 total points
ID: 35475196
ALTER TABLE <table name> ALTER COLUMN <column name> COUNTER (<start number>,<increment>)
0
 

Author Comment

by:Bevos
ID: 35475285
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
0
 
LVL 17

Assisted Solution

by:Chris Mangus
Chris Mangus earned 668 total points
ID: 35475595
I'm curious, since you're using an auto-generated value that has no meaning, why do you want to start it at 10000?  After 90,000 records it's going to go from 5 digits to 6 so you'll lose any formatting you many have wanted.

If you want a specific formatted number I'd use a VBA routine that generates what you want.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Assisted Solution

by:Runrigger
Runrigger earned 1332 total points
ID: 35475678
It's an action query, simply open a SQL query and execute, click through any warnings popups (read them first, click OK if you are happy)
0
 

Author Comment

by:Bevos
ID: 35475751
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.
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 35475839
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.
0
 

Author Comment

by:Bevos
ID: 35476410
Could you please briefly explain split design?
0
 
LVL 11

Expert Comment

by:Runrigger
ID: 35476455
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
0
 
LVL 11

Expert Comment

by:Runrigger
ID: 35476475
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.
0
 

Author Comment

by:Bevos
ID: 35476562
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
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 35478514
I am male...and no offense was noted  :)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question