Help! Archiving records in an access database

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

Can please somebody help me with archiving the records from an ms access
database, by date (all the records before the specified date-specified in a
msg. box or in a textbox/combo box f a form) ?
Thank you!
 
Chris,

This is a Romanian language newsgroup, and I strongly advise you to post your question in an English newsgroup to obtain an answer more quickly.

About the question you have asked, you might want to find out that Microsoft Access does not record the timestamp of insert or update operations which are executed on the database tables' records.

If you included a timestamp column on the tables that you want to archive, you will be able create a custom backup application which will browse the records and move them to another Access database (perhaps with the same structure) which will contain the "archive", but otherwise, I'm not sure that your task will be easy.

--
Sorin Dolha [MCP, MCAD]
Can please somebody help me with archiving the records from an ms access
database, by date (all the records before the specified date-specified in a
msg. box or in a textbox/combo box f a form) ?
Thank you!
 
Archiving is a two step process:
- Run an append query to copy the records to a target table.
- Run a delete query to remove them from the original table.

To make an action query:
1. Create a query into your table.

2. Change it to an Append query (Append on Query menu).

3. To specify the date, create a form named (say) "MyForm", with a text box
named "Mytextbox". Set the Format property of the text box to Short Date so
Access knows it's a date.

4. In the Criteria row under your date field, enter:
< [Forms]![MyForm].[Mytextbox]

5. Make a similar delete query.

6. Run them both.

If it is important to you that the delete query does not fire if the append
query failed, you may want to write code that wraps both action queries in a
transaction, so you can rollback if necessary. Details of how to do this in
article:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html
 
Chris

Are you quite certain that what you mean by "archive" and what all 'group
readers mean is the same? <g>

What business need are you attempting to solve by "archiving" (whatever that
means to you)?

Is there a chance that any of the "archived" records might need to be
revisited or displayed?

Are the records in a single table, or are you considering a combination of
related rows in multiple tables to be your "record"?

One approach to making certain rows in a table "historical" or "archiving"
them is to add a field that holds either a Yes/No value (?Archived = Yes),
or a Date/Time field that holds a "Date Archived". Either way, you can then
use queries to select all rows without that "archive" setting for normal
display, and a query that returns all rows for a historical display.
 
Back
Top