Archiving tables (export several tables to a new database)

[Guest]

I want to be able to archive several tables from my database on a yearly
basis for all data from that year. (the year is recorded as a text file so i
can select rom a list of academic years 05/06, 06/07 etc). The 2 tables i
want to archive are a "Training course" table and a "registrations" table
that records the individual course registrations of each participant.

Every year i want to export all the data from both tables through a simple
form control and place the results in a different database removing the data
but not the format/relationships etc of the exisiting tables.

What is the best way to do this. I might settle for a control that exports
all the data from each table but would prefer to do it for the query on
Academic year. I'm getting stuck with i was thinking of a a make tabel query
followed by a delete query for both tables.. then exportign the 2 tables. I
want the data to copy to anotehr database that i will creat that wil maintain
tehrelationship between the data.

Can anyone suggest a structure to my madness?

 

[tina]

to export data to identical tables in another database, just link the
external tables into your database, then run append queries *from* the
native tables *to* the linked tables; then run delete queries on the native
tables.

note that archiving data out of your working database is usually
counterproductive, unless the table/database size is approaching the limits
listed in the Access specifications for the version you're using. by keeping
"prior year" data in their tables, and adding a date field to parent tables,
you can easily restrict your queries (and therefore forms and reports) to
current-year data. this method has the added advantage that you have full
access to historical data using the existing form and report objects, by
simply changing the date criteria in your queries to the year you want to
see. an added advantage is the ability to statistically compare current data
to historical data.

hth

 

[Guest]

Ok thanks i think you are right i shouldn't be archiveing the data unless i
have to.. which i probably don't. I already have an Academic year field
recorded so i will probably jsut redesign my forms/queries etc

thanks

 

[tina]

you're welcome

Ok thanks i think you are right i shouldn't be archiveing the data unless i
have to.. which i probably don't. I already have an Academic year field
recorded so i will probably jsut redesign my forms/queries etc

thanks