Need help rebuilding database in Access 2002

[lofisamuri]

Ok, here goes. First off, I am by no way proficient in Access, it just happens that I am the best person in our office to get it done. That being said, I am not even sure if this has been covered elsewhere on the forums.

We are using Access 2002, our database became corrupt when my boss was backing up the database. So I am rebuilding from a recovered database (I used access recovery demo) which brought back some of our contact information and all the fields we use. So now I've been entering the remaining entries from a hardcopy printed out. The entries have contact ID numbers which is a shared control (I think that's what the message said) and it is also the key. Problem is when I pulled up old contacts some already had a contact ID, but not in order. (ie: 2, 5, 9, 13, 23, 47...) Once I got up to my 47th entry I found I already have a contact ID # 47. Now I need to go back and change the contact ID numbers so they read consecutively in sequential order. I have not been able to figure that out yet.

If anyone can please guide me to any threads where this is discussed or point me in a right direction I would greatly appreciate it. I've been slowly working on rebuilding this for almost two weeks and I am about done with it.

As well, if anyone has suggestions to online help (other than mocrisuft) I would be very happy.

Thanks in advance to any help.

Troy

 

[CHPD]

If I'm reading your post correctly this should help?

If you just want to view your data in order you can click on the sort ascending icon on the toolbar. The icon has the letters A & Z with an arrow pointing down. Hope this helps.

 

[scroce]

I may not have read you correctly, but can you do something like this? . . .

I think what you need to do is go into the design view of the table, and remove the key designation on the current key field, contactID.

Also make sure it is not an autonumber - if it is, then change it to a regular number field.

Then add a new field called contactID2 and set it to be an autonumber field. This ought to regenerate a the sequential numbering you need.

Set contactID2 to be the primary key field, delete the original contact ID field.

Then rename contactID2 back to contactID

don't know if that's it, but maybe it'll get you closer to your goal.

Steve

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[lofisamuri]

Thanks for such a quick answer, but that's not quite what I need. I want to remove the old contact ID numbers (that are sequential but varied ie: 2, 5, 9, 14, 25, 47...) My contacts themselves are in alpha order, not the contact ID #'s. I want to remove them and re-number them in order 1, 2, 3...

But when I try to do any type of editing to the contact ID it stops me saying I cannot change because it is bound to auto number field.

 

[lofisamuri]

Steve,

That sounds pretty darn close to what I am looking for. I will try that out and post my results here shortly.

Thank you both for such quick replies. I really appreciate this forum, tek-tips has saved me quite a few times.

Troy

 

[lofisamuri]

OK, I am working on changing the key field (Contact ID) to a number field so I can renumber. Now I get the message that I cannot change or delete because it is a part of one or more relationships.

Our database has a few tables and all were made in the design wizard, all are related by the contact ID. I'll need to remove the relationships to complete the changes mentioned above.

Before I do so, does this sound right?

 

[scroce]

well, that's a problem. - if you wipe out the ID's and then change the numbers, access won't know how to relate the records to the linked tables. I think you'd have to find a way to update the foreign keys to the new primary key.

How hard would it be to match it up manually? or to use update queries to update the old contactID's with the new ones? i.e. are there a lot of linked tables with a lot of linked records?

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[lofisamuri]

Scroce,

Sorry I didn't get back to this earlier, I've been out from work. I just tried your remedy creating ContactID2 and deleting the old ContactID, making the ContactID2 my primary key and renamed it ContactID.

I double checked all the entries and they are now sequential and in order!!

All of our tables/queries/forms were created with the Wizard, so they shared the contactID relationship but generally never used.

Thank you again for your expertise. I really appreciate your assistance and the tek-tips forums.

Troy

 

[scroce]

great! Glad to help.

I am a nobody, and nobody is perfect; therefore, I am perfect.