Archiving Records in Access DB

[Jeff Gaines]

I have written an app for my daughter in C# with an Access 12 database
holding the data. It creates html pages for a website based on details
provided by customers. She is adding around 200-300 records a month, so
2,500 to 4,000 records a year. When the period for showing the record on
the website expires (60 days) the records are flagged.

I need to consider the best way of archiving records once they no longer
need to be shown on the website. I could just leave them in the main table
and use the flag for selection or I could move them out to an archive
table, or even to separate archive tables for each financial year.

The easiest way is to leave them in the main table and use the flag for
selection but I am not sure what performance hit this might cause. Is it
much slower to search a table of, say 12,000 records against a table
of,say, 1,000 (representing the current live records)?

Any suggestions for 'best practice' would be appreciated

 

[Allen Browne]

Try it. My guess is that it will make no difference. 12k records is tiny for
Access.

If you want to always, automatically expire records after 60 days, just
include a DateEntered field in your table. Set the field's Indexed property
(lower pane of table design) to:
Yes (Duplicates Okay)
Create a query with this in the Criteria row under this field:

Date() - 60

If you want to manually expire selected records, use a yes/no field named
(say) Inactive, and check the boxes to expire the records. Again, set the
Indexed property, and use a query to select those where Inactive = False.

If you really want to move expired records out to a different table, here's
how it's done:
Archive: Move records to another table - copy + delete in a transaction
at:
http://allenbrowne.com/ser-37.html

 

[Jeff Gaines]

Try it. My guess is that it will make no difference. 12k records is tiny
for Access.

OK, I will try it and see, it would certainly make things easier.

If you really want to move expired records out to a different table,
here's how it's done:
Archive: Move records to another table - copy + delete in a transaction
at:
http://allenbrowne.com/ser-37.html

I have to say I find your website enormously useful, thank you

I tend to use C#, is there an equivalent of DBEngine(0) in C#? I can
generally manage to convert VB/C# but that one always throws me!

 

[Allen Browne]

dbEngine is provided by the DAO library.
You can also execute query statements with the ADO library if you prefer.

If you need to use C/C++ on the A2007 for some reason, this might help:
http://msdn.microsoft.com/en-us/library/cc811599.aspx

 

[Jeff Gaines]

dbEngine is provided by the DAO library.
You can also execute query statements with the ADO library if you prefer.

If you need to use C/C++ on the A2007 for some reason, this might help:
http://msdn.microsoft.com/en-us/library/cc811599.aspx

Many thanks