A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Archiving Records in Access DB



 
 
Thread Tools Display Modes
  #1  
Old February 8th, 2009, 10:29 AM posted to microsoft.public.access.tablesdbdesign
Jeff Gaines[_2_]
external usenet poster
 
Posts: 6
Default Archiving Records in Access DB


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 :-)

--
Jeff Gaines Damerham Hampshire UK
That's an amazing invention but who would ever want to use one of them?
(President Hayes speaking to Alexander Graham Bell on the invention of the
telephone)
  #2  
Old February 8th, 2009, 10:46 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default Archiving Records in Access DB

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jeff Gaines" wrote in message
...

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 :-)

--
Jeff Gaines Damerham Hampshire UK
That's an amazing invention but who would ever want to use one of them?
(President Hayes speaking to Alexander Graham Bell on the invention of the
telephone)


  #3  
Old February 8th, 2009, 04:34 PM posted to microsoft.public.access.tablesdbdesign
Jeff Gaines[_2_]
external usenet poster
 
Posts: 6
Default Archiving Records in Access DB

On 08/02/2009 in message Allen
Browne wrote:

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!

--
Jeff Gaines Damerham Hampshire UK
That's an amazing invention but who would ever want to use one of them?
(President Hayes speaking to Alexander Graham Bell on the invention of the
telephone)
  #4  
Old February 9th, 2009, 01:42 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default Archiving Records in Access DB

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jeff Gaines" wrote in message
...
On 08/02/2009 in message Allen
Browne wrote:

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!

--
Jeff Gaines Damerham Hampshire UK
That's an amazing invention but who would ever want to use one of them?
(President Hayes speaking to Alexander Graham Bell on the invention of the
telephone)


  #5  
Old February 9th, 2009, 10:21 AM posted to microsoft.public.access.tablesdbdesign
Jeff Gaines[_2_]
external usenet poster
 
Posts: 6
Default Archiving Records in Access DB

On 09/02/2009 in message Allen
Browne wrote:

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 :-)

--
Jeff Gaines Damerham Hampshire UK
That's an amazing invention but who would ever want to use one of them?
(President Hayes speaking to Alexander Graham Bell on the invention of the
telephone)
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:44 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.