By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,850 Members | 972 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,850 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.
Jun 27 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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...@NOTHINGHEREbrunsley.com.auwrot e:
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.
Jun 27 '08 #2

P: n/a
On Tue, 10 Jun 2008 09:51:25 -0700 (PDT), timmg
<tm*************@gmail.comwrote:
>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.
Jun 27 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.