Restoring deleted records into a table with an auto number field?

[Regression]

Hello All,

I have a problem. This morning about 40 clients were deleted out of my DB. I wasn't informed of this until the end of the day today.

I need to restore these clients to the database. The problem is they have customer numbers that are created by the auto number field in the DB.

Does anyone know how to merge these records back into the table and allow them to still keep their originally assigned customer ID?

If you need more information let me know what I left out.

Thank you all!

 

[JeremyNYC]

I'm not sure what to suggest here. You could think about restoring the entire old table, updating records that didn't get deleted, and re-adding records that were deleted today. There may be slicker ways of doing this, but I'm not coming up with any right now.

I do have one point to make taht may make you want to smack me, though I hope not, as it's well intentioned...whenever possible, it's best to create systems whereby records are never actually deleted, they are merely flagged as obsolete with a yes/no field. This makes situations like this a lot less painful. There are caveats to that, of course, such as wanting to destroy credit card data, etc.

I'm sorry you have to deal with this, and I'm sorry to be offering you a preventative method after the damage has been done.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[Maquis]

Jeremy has good advice there about deleting. Unfortunately you are learning the hard way.

Do you have nightly backups? If so, the easiest way would be to restore last night's backup and then re-type in any new clients which were added today.

This is also another reason why auto-numbers really shouldn't be used for data fields. I've run into the same problems with auto-numbers resetting themselves and thereby corrupting data in related tables. A good substitute would be to use a number field and write a VB routine to "auto-fill" the number with a counter. Maq
<insert witty signature here>

 

[JeremyNYC]

Maq,

I have to disagree with you on autonumbers. There is no reason not to use them. Just use them along with thoroughly planned database practices and you won't have a problem.

Autonumbers do not corrupt data in related tables, and they do not reset themselves. If you compact they will start at the number one higher than the highest already in the table. As designed. As documented. If you have records in another table that refer to autonumbers that no longer exist, that's because you're not enforcing referential integrity with cascading deltes, either through Access's built in mechanism or some other way.

jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[gusbrunston]

[tt]
Hi:

I'm &quot;hitchhiking&quot; on this thread with another question.

I have never used &quot;synchronization&quot;, know nothing about it. But, would it be possible to use a recent backup db, &quot;synchronize&quot; it with the db where the records were deleted, and come out whole?

Thanks,[/tt] Gus Brunston An old PICKer, using Access2000
[tt]Want solutions you can understand?
Post understandable questions.[/tt]

 

[Regression]

Guys,

Thanks for the input. I agree with your never truely delete theory and try to stand by it.

The database in question was unfortunately engineered before I started my tenure here. I would have created the same problem with the way I usually delete. I normaly move records from the active table to an archive table. This keeps the records in the system however, it would still create my autonumber key issue so perhaps I need to rethink that portion.

I have corrected thi issue I did it by appending the deleted records to the old table. This gave them new CustID's I then went into the assosiated tables and changed the records that corresponded to them to reflect their new CustID.

Far from perfect and very frustrating but effective.

I amk also curious to know if the syncro approach would work. Anyone have an Idea?

 

[gusbrunston]

[tt]
Since I asked, I did some studying on &quot;Replication&quot;, (&quot;synchronize&quot;, I now know, is a sub part.)

There is a procedure for Making a &quot;Master&quot; db and from that creating &quot;Replicas&quot;. These Replicas can then be synchronized to the Master. Looks like it is most useful for travelers, bringing their notebooks back to feed their new data to the master beast. Also, on the road, they can update the db they have with them over the phone.

I'm looking into whether it might be a simple way to distribute changes to the Front End to all the work stations.

Ya' think?

Cheers, Gus Brunston An old PICKer, using Access2000
[tt]Want solutions you can understand?
Post understandable questions.[/tt]

 

[JeremyNYC]

Gus,

Replication is a pretty sticky thing, and I'm pretty sure it only deals with data, though I'm not positive on that. I do know that no one I know uses replication to roll out front ends.

It's not that bad to set up a system whereby you only roll out a new version to the user when it's needed. I've got code and text about that on my website.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[gusbrunston]

[tt]
Thanks, Jeremy.

I suspected as much.

I'll look on your site for the code.

Again, I appreciate your help.[/tt] Gus Brunston An old PICKer, using Access2000
[tt]Want solutions you can understand?
Post understandable questions.[/tt]

 

[Regression]

I just wanted to thank you guys for the input. I have created a little sample database for myself which allows me to archive old records and then restore them if needed.

I am planning to use this type of construct in all my future works and possibly work on redesigning our current databases to allow for this.

Thanks Again. =)