Re-creating a deleted record and keeping the same value in an Autonumber field

[Richard Sherratt]

I've inherited a system that was designed by someone with no
understanding of database design. From the logical design point of
view, there is no logical design. The physical design is a nightmare.
e.g. there are no relationships between tables and foreign keys appear
to have been sprinkled at random like confetti. As an example, the
Employer - Employee - Contract - Expense hierarchy has EmployerID in
the Expense table. That's the background.

Someone has deleted a Contract record. I need to re-create it with the
same ContractID, an Autonumber field. I can open the table in design
view, change the Autonumber to a Long, go to datasheet view and add
the new record. Then if I open the table in design view, do a save as
with another name, change the key field in the new table back to an
Autonumber, I can run an append query to copy all the records from the
old table to the new one while keeping the Autonumber field values. A
bit of table re-naming, and we've got the old record restored.

I've tested it and it seems to work OK.

Are there any problems with this?

Regards,
Richard.
--
Regards.
Richard.

[timmg]

Re: Re-creating a deleted record and keeping the same value in anAutonumber field

Might be some problems w/ referential integrity, but it sounds like
that might not be in force.

However, you can run an append query against an autonumber field and
restore the value that way. Lemme know if that interests you and
you're having any troubles. You might have to go to VBA to do a
docmd.runsql statement.

Anyway, changing the data via query should be a more elegant solution
to a sub-optimal application.

best of luck,

Tim Mills-Groninger

On Jun 10, 5:52 am, Richard Sherratt
<richard.sherr. ..@NOTHINGHEREb runsley.com.auw rote:

I've inherited a system that was designed by someone with no
understanding of database design. From the logical design point of
view, there is no logical design. The physical design is a nightmare.
e.g. there are no relationships between tables and foreign keys appear
to have been sprinkled at random like confetti. As an example, the
Employer - Employee - Contract - Expense hierarchy has EmployerID in
the Expense table. That's the background.
>
Someone has deleted a Contract record. I need to re-create it with the
same ContractID, an Autonumber field. I can open the table in design
view, change the Autonumber to a Long, go to datasheet view and add
the new record. Then if I open the table in design view, do a save as
with another name, change the key field in the new table back to an
Autonumber, I can run an append query to copy all the records from the
old table to the new one while keeping the Autonumber field values. A
bit of table re-naming, and we've got the old record restored.
>
I've tested it and it seems to work OK.
>
Are there any problems with this?
>
Regards,
Richard.
--
Regards.
Richard.

[Richard Sherratt]

Re: Re-creating a deleted record and keeping the same value in an Autonumber field

On Tue, 10 Jun 2008 09:51:25 -0700 (PDT), timmg
<tmillsgroninge r@gmail.comwrot e:

>Might be some problems w/ referential integrity, but it sounds like
>that might not be in force.
>
>However, you can run an append query against an autonumber field and
>restore the value that way. Lemme know if that interests you and
>you're having any troubles. You might have to go to VBA to do a
>docmd.runsql statement.
>
>Anyway, changing the data via query should be a more elegant solution
>to a sub-optimal application.
>
>best of luck,
>
>Tim Mills-Groninger

Thanks, Tim.

Normalisation, logical database design and referential integrity are
just three of the terms that were not allowed to interfere in the
original cobbling together of this system :-)

--
Regards.
Richard.