Archiving old records

[dean]

I have created many databases and linked to the frontend. I have one database for transactions only. And I have other databases for customer and vendors.



Since my database for transaction records is getting big, I am wondering how can I archive old records (years) so that only current (year only) record is linked? I have no even a single idea how to archive.

Can anybody show me how they archive their old records? Thanks!

[dcrake]

The easiest way to archive records is to create an Archive mdb with the tables in that you want to archive.

Then follow the next steps

1. Create an append query based on the table(s) you want to archive using a filter.
You need to specify that the data is being appended into the table in your archive.mdb.

2. run the append query

3. Check that the append query worked

4. delete the contents from the live data using a delete query with the same parameters

If you need to look at the archived data you can link the tables back to the transaction mdb and create union queries to interoogate if the parameters means you need to use both tables.

David

[dean]

ok. that's how it is done. thanks for giving me idea. But what if I need those old records again and my current database cannot accomodate to bring back those old records, is there a way to put the records there to show the old and current records?

[dcrake]

Lets say you are archiving all transactions for 2008. When you append the data to the archive mdb call the table Arc2008 or something meaningful that will identify the period of data held within it.

Then in the current front end mdb link this table. you can now use a union query

 
Select * from TblTransactions
 
Union All
 
Select * from Arc2008

Now when you open the query you will see data from both the current and the archived data.

David

[dean]

wait a second. do you mean that for example as I can understand it, let us say I have:

a. mdb (current 2009 database)
with 1 table named TblTransactions

b.mdb (archived 2008 database)
with 1 table named TblTransactions1

code:

Select * from TblTransactions

Union All

Select * from TblTransactions1

do you mean that by using the code above will let me see the current and archived records?

Can I also make additions or edits that way? Meaning I can use that query as record source in a form to add and edit records?

[dcrake]

Union queries are not updateable, as far as I know, but you can use the linked tables for editing etc.

You can have as may back ends as you want and link them to another which can then be linked to the front end

BE 2006 - 2006 data
BE 2007 - 2007 data
BE 2008 - 2008 data


BE current - 2009 data
Linked BE 2006
Linked BE 2007
Linked BE 2008


Front End - Linked - BE Current

Union Query

Tbl2006
Tbl2007 +
Tbl2008 +
Tbl2009 +
--------
All Data
======

David

[dean]

ok so it is not updatable.....is there a way to make it updatable...maybe other way to link those record and updatable!

[dcrake]

The way to do this is to base your search on the union query then by identifying the specific record you want you call the record in the appropriate table to edit.

Lets say you added and extra column to your union query that was the name of the source table and used this for reference.

David