Append & Delete Sequence

  • Thread starter Thread starter Melissa
  • Start date Start date
M

Melissa

I have a recruiting database consisting of these three
tables (among many others):

tblRecruits
tblInteractions
tblEvaluations

I have a one-to-many relationship between tblRecruits and
tblInteractions, and the same between tblRecruits and
tblEvaluations. I have enforced referential integrity and
have cascaded updates but not deletes (because I have
been warned that it can lead to unintended deletions).
These relationships are set up in the relationships
window of the database.

At the end of the year, I want to be able to run a macro
that accomplishes the following:

1. Asks user to Enter Class to Delete.
2. Appends all recruit records to tblRecruitArchives
(because in rare instances user may need to retrieve
these records).
3. Appends all related recruit interactions to
tblRecruitInteractionsArchives.
4. Appends all related recruit evaluations to
tblRecruitEvaluationsArchives.
5. Deletes appended recruit, interaction and evaluation
records from the original tables.
6. Compacts and repairs database.

My questions are:

A. Is this possible? If so, please give specific
instructions. I know how to create append and delete
queries, but I am a rookie when it comes to macros.

B. In running an append and delete macro, the warnings
for appending and deleting appear. This would be fine for
verifying the number of recruits...but I do not want the
user seeing warnings about interactions or evaluations.
Is this possible?

C. If I change the relationships to cascade delete, will
this simplify the whole process?

D. Can you give me specific information on how to program
the compact and repair portion of this process?

Many, many thanks for your help.
 
Melissa,

....
A. Is this possible? If so, please give specific
instructions. I know how to create append and delete
queries, but I am a rookie when it comes to macros.
Click to expand...

Yes, this is certainly possible. Just make your Append and Delete
queries, and then use OpenQuery actions in your macro, one for each query.
B. In running an append and delete macro, the warnings
for appending and deleting appear. This would be fine for
verifying the number of recruits...but I do not want the
user seeing warnings about interactions or evaluations.
Is this possible?
Click to expand...

Just put a SetWarnings/No action in your macro, ahead of the first
OpenQuery action.
C. If I change the relationships to cascade delete, will
this simplify the whole process?
Click to expand...

Yes. It would mean you just have to run your Append queries for the 3
different data sets, and then just one Delete query for the tblRecruits
table and the related data in the other two tables will be deleted. I
can't see how this could cause a problem, and this is certainly the way
I would do it. On the other hand, having Cascade Updates set on your
relationships is probably pointless.
D. Can you give me specific information on how to program
the compact and repair portion of this process?
Click to expand...

Do you have your data in a linked backend .mdb file, or is everything
all in the same file?
 
Steve:

Right now everything is in the same file. I am
considering splitting the database into a front end and
back end in order to make it run quicker. Some of the
forms are taking a bit of time to open.

Thanks for your help.
 
Melissa,

There are a number of reasons for splitting a database, particularly in
a multi-user environmnet,... but speed is not one of them. It will not
make your forms open any faster. In fact, you can expect a noticeable
performance drop, maybe in the order of 15%.

If you are using Access 2000 or later version, you can set the database
to 'Compact On Close', under the General tab via the Tools|Options menu.
 
Back
Top