Doug
A check through the tablesdbdesign newsgroup will reveal something of a
consensus against removing records from one table (and/or database) and
putting them in another. The two reasons I see for this are that it is
unnecessary work (key word, work), and that it makes "spanning" the data
(looking up across all available data) much more difficult.
It isn't uncommon to find Access databases in the 100's of Mbytes, so a db
with under 10 MB is not particularly large.
If this is an "exercise", by all means, knock yourself out! Be aware that
you'll need to backup the "old" database, build several queries (or the
underlying SQL statements), select the records for archiving, append the
records to the new location, confirm that they've safely arrived, backup the
new database, delete the records from the old database, and, if those
records were in any way related to other tables' records, clean up all the
related table records too.
On the other hand, the commonly-used approach to not wanting to see "old"
records in a "current" database is to simply add a field to the table. If
you don't care when a record was archived, it can be a Yes/No field (e.g.,
[Archived?]). If you want to know when, make it a Date/Time field (e.g.,
[DateArchived]).
Then modify your queries and the forms (reports, etc.) that they feed to
exclude any records with that "archived" field set.
JOPO (just one person's opinion)
Regards
Jeff Boyce
Microsoft Office/Access MVP
Doug_C said:
Why not??? Is there any specific reason why you are against doing this?
I'm
not sure I understand. Based on your response, please clarify your
reason(s)
other than this is not the way you would handle it. The database is small,
about 35,000 records and maybe 7 or 8 MB. The reason why is just because I
want to learn and know how to do it. This way, I would have accomplished
learning a new task so when I do have an extremely large database I can
create the archive myself which is just more knowledge to gain and expand
my
skills. I like to learn and have learn quite a bit through this site and
have
appreciated everyones help and cooperation in obtaining that knowledge.
No performance issues, just to do it because I know it can be done.
Personal
preference.
The users and myself can open the archive database which will be in the
same
folder and can do a search in that database if infomation is to ever be
obtained. This would be in a rare situation which is another reason why I
don't want it in the primary database. Why keep that database cluttered
with
information that may be needed once or twice in a year or more. Basically,
it's not much more effort for someone to open the other database once or
twice a year if even that. The one database currently set up this way, we
never looked in the archive since inception which has been over three
years.
Thanks!!!!