Archiving data

[odrap]

For archiving purposes,at the end of each year, a procedure is running that selects all records in the tables tblOrderdetails and tblOrders, of which the orderdate dates from two years ago: e.g.at the end of 2009 this will be all records with an orderdate = 2007.
This records are put respectively in two other tables that are given the names : tblOrderdetails07 and tblOrders07. Once this tables are made and filled with the records, the same records in the tables tblOrderdetails and tblOrders are deleted.
Although i"m working with linked tables, the procedure put this new tables are placed in the front end and not in the back-end.
I have the VBA code to realize what is explained hereabove, but i don't know how to adapt the code in such a way, that the new tables are constructed and filled in another database with the name Archive.accdb that exists in the same folder as the front-end.
Futhermore I would like to know how i can retrieve information out if this tables from the frontend of my database.

 

[jzwp22]

I don't think I can answer your question directly, but I can offer an alternate way. From your archive database, go to tables--New-import and the find your main database (backend I assume) and select the order/order detail tables. Then go to options and select definition only. This will duplicate the structures of your order and order details tables in your archive database. Then go to your front end and link to those tables. They will have the same name as your main tables but will have a 1 after them. Now, at the prescribed time, you can just do a simple append query to select the records from your main tables and dump them into the archive database's tables. Then you can run your delete query to get rid of the same records in your main tables. To view that old data, you can set up forms/queries that reference the order1 and orderdetail1 tables from your current front end or you can duplicate your front end and link to just the archive backend.

I would keep adding records to the same tables in the archive database; I would not label the archive tables with the year, that has a tendency to limit your options.

I would guess that you will eventually get rid of records in the archive database at some point--maybe after 5 additional years or something like that.

 

[pbaldy]

I wouldn't archive records unless you're dealing with a massive number of them per year and you experience performance problems. That said, the way to make a table in the archive db from the front end is with the IN clause:

CurrentDb.Execute "SELECT * INTO NewTable In 'c:\Archive.mdb' from OldTable"

You can also use the IN clause with an append query.

 

[odrap]

Thank you very much