Archiving Records

[Guest]

I have inherited a database that is growing daily, and is split over a
network.
Currently 4563 records, 32 fields in tblCurrentRecords, residing on the
server.

Deciding to archive records from tblCurrentRecords to tblArchivedRecords;
.. I copied structure of tblCurrentRecords to create tblArchivedRecords,
.. created qry to append records to tblArchivedRecords from tblCurrentRecords
.. criteria used is [tblCurrentRecords ].[OutOfServiceDate] <(Date()-90)
ie 90 days old.

However, it does not append 69 records due to validation rule violations.
Why such an error when I copied structure of tblCurrentRecords to create
tblArchivedRecords?
I am assuming it is pre-existing records before my data validation and error
trapping was put in place? Any ideas?

And, if i use a delete query to remove the same records from
tblCurrentRecords to complete the archive process, I lose 69 records - the
delete query does not discriminate. I want to automate the process, eg macro
at startup daily, but will I be cautioned if Access can't append records in
the future?

 

[John Vinson]

I have inherited a database that is growing daily, and is split over a
network.
Currently 4563 records, 32 fields in tblCurrentRecords, residing on the
server.

<checking>

One of my clients has a table with 142488 records.

4563 records is *tiny*. With proper indexing, you can handle MUCH
larger tables.

Deciding to archive records from tblCurrentRecords to tblArchivedRecords;
. I copied structure of tblCurrentRecords to create tblArchivedRecords,
. created qry to append records to tblArchivedRecords from tblCurrentRecords
. criteria used is [tblCurrentRecords ].[OutOfServiceDate] <(Date()-90)
ie 90 days old.

This will not help much; the records are still in the same database,
and the overall database size has an effect in addition to the effect
of the size of the table itself. If you wish to archive, archive to an
external database.... say when you have about a thousand times more
records than you do now.

However, it does not append 69 records due to validation rule violations.
Why such an error when I copied structure of tblCurrentRecords to create
tblArchivedRecords?
I am assuming it is pre-existing records before my data validation and error
trapping was put in place? Any ideas?

That, or you have relationships which are prohibiting the action.

And, if i use a delete query to remove the same records from
tblCurrentRecords to complete the archive process, I lose 69 records - the
delete query does not discriminate. I want to automate the process, eg macro
at startup daily, but will I be cautioned if Access can't append records in
the future?

Depends on how you do it. If (and I'd suggest that you DON'T) you want
to do this, use a Transaction in VBA code to ensure that the delete
query runs only if the append query is error-free. See "Transactions"
in the VBA online help, or post back - it's not too difficult but it's
not trivial.

John W. Vinson[MVP]