Adding Serial Number to an already existing access document

20 pts.
Tags:
Access 2003
Access Database
Microsoft Access
Hi , I want to add a row of serial number for the existing records in MS access .Whenever I am trying to define a new row with type as "Auto Number" and save the same it automatically gets changed to Text type.Please suggest. Thanks, Ansh
ASKED: February 24, 2009  2:46 PM
UPDATED: March 22, 2010  5:51 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

MS Access won’t let you create an autonumber row after you have already entered data into the table. What I suggest is to copy the table, structure only. Add the autonumber field, then append the data in the old table to the new table and Access will assign the autonumbers. Hope this helps.

Dustin

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Ansh123
    Hi Dustin, Thanks for your prompt reply.Actually the requirement is modified and now the serial number has to be of 8 digits i.e. the first number should be 00000001 and the max length should be of 8 digits as this field is being received by an external system and this system will accept only 8 digits .Is it possible to do in MS access and how can I do the same. I am allowed to use VB as well,so can anyone please let me know how it can be done through any of these.
    20 pointsBadges:
    report
  • Khalidkhan
    Brilliant solution
    10 pointsBadges:
    report
  • Chippy088
    I am not sure, but it might be you can only do it with a text field, as it seems you can't define a mask for the primary key unless it is of type data or text. You might be able to do that in vb, but if you want to keep it simple, use a text based field with a numeric mask, (i.e. only allow the characters 0-9,) and only pass that to the external system to be imported as the primary field. Down side is the user will have to type the 8 digit number in each time an entry is made, as I don't think you can autoincrement a text field, so data validation rules then come into effect. Or Use an auto incremented numeric primary key and pass the data table through vb to change the numeric key into an 8 character text field. I take it the field is imported into the external system as text, as it will loose the leading zeroes if it converts it into an integer/number type index on import. Personally the first option is the one I'd use. It could be tested for incorrect serial entry by trying to convert the serial number into a long int using vb before export to external system, as errors are generated if alpha chars are in field. This seemingly simple need does not seem to be catered for by access without some form of addin, if there is one. Good luck, and would you post your solution if you succeed.
    4,625 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following