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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|