Archiving Basics ???

[claddagh]

Hi Guys,

Im about to create a button on a form that will archive the current record to an archive table.....

The question is, Before I start to look into this, I would just like to clarify what I do.

I assume firstly I would need to make an exact copy of my current table, them remove all data, then change name to something like "tbl_archive_data"
( this would be to hold the archive info right ? )
Once I have done this, I`m assuming i`ll have to make some sort of query that will copy the data to the new table, then delete the record from my current table ?
Then I`ll have to create a button on my form, which can be cliocked on by the user to archive this particualr record...

In a nutshell, would this be sort of the correct way to do this ???


Any recommendations/advice will be much appreciated on this.

I know there is already some archive details in the forum, but none of them really made much sence, as they were answers to problems with this already half done....

Thanks in advance guys.

Max

 

[andrew93]

Hi Max

In a nutshell : you are on the right track. These are just my thoughts on the scenario you described - other posters may have other opinions, but this is how I would approach it.

In your data table(s) have a yes/no field called archived (default = no). When the user wants to archive the record, they tick this field. The combo boxes, forms and reports exclude any 'archived' records so that from the users' perspective, the records are no longer there.

If you want to actually remove the records from the data table(s), but keep a copy of the data, then there are 2 choices - either make a full copy of the data or a summarised version.

One way is to copy the data across in it's entirety, which requires another table with the identical strcuture (as you described). What you would do in this instance is create an append query that copies the records where archived = yes from one table to the other, and once that is completed you delete the records from the main table where archived = yes. With this approach the records appear to be archived when the user ticks 'yes' (as I explained in the previous paragraph), but the actual archiving can be done at a more opportune time (e.g. when opening or closing the database, when all records have been selected and the user clicked the archive button, as an administrator function etc.) In other words, the archiving does not have to be in 'real time' but at a time that suits you.

The 2nd way is pretty much the same, except you copy across a condensed version of the data and then delete the detailed data. This depends on the type of data you are trying to archive. For example, with something like daily product sales by store, you might delete all of the detailed records but archive one record for total daily sales, and maybe copy across the daily totals for each store and the daily totals for each product (in seperate tables, if you want that info). For this approach the user is not clicking 'yes' to individual records, but to entire blocks of data instead, or archiving data that meets certain criteria (e.g. before a set date, or data that is more than x days / months / years old).

You are on the right track. My advice would be to experiment and see what works best for you given the data you are working with. Keep in mind that Access can handle hundreds of thousands of records very easily (I have seen databases with >1.5m records) so don't be too quick to archive data unnecessarily.

HTH, Andrew

 

[claddagh]

Thanks

Andrew,

Thanks for the info.

The way i have done this at the min,
I have created an exact copy of the table with the data that i`m archiving, called tbl_archived_data
Then deleted all the data within.
I have also coped the form that this data is viewed in too called frm_archived_data.
This is as I still need to be able to see the archived data if need, and have added this into an administive tool option within the switchboard.
At the min though, I have to manually get the use to tell me which records need to be archied, then I manually copy, paste and delete from and to the rewuired tables, which is working fine at the min, but I can for see this to be a total pain in the arse for me in the future.

What Im looking to do, is as you suggest is to automate this, copy, past, delete.

I have looked into what youhave said for tyhis using the append query, but I can not got it to work.

I have looked into some axamples, but am gwetting more confuseed.

Any pointers to examples would be appreciated.

Thanks for the responce, as this has already helped me in a big way.

Max

 

[claddagh]

Half Sorted

OK, I have now created my append query that works well, based on a Yes/No Field archived.
This is now a button in my admin section of the database.
Now i have to remove the records from the previous tbl tbl_customer_data
I assume I need a delete query, but am a little lost on this one, and am cautios that I dont delete the wrong data.

Im assuming whis would be almost identicle to the append query, but as a delete query.

Any help much appreciated.

Thanks in advance

Max

[edit]

Also, Say I wanted to restore the record, I could just reverse the process right ? Eg, start with the archived table going to the origanal table ?
Using the same query & delete query ?
Or is this becoming a bit risky ?

Please advise..

Max

 

[andrew93]

Hi Max

When you create the delete query (Click Query -> Delete Query), click and drag the * from the table into query grid, it should show 'Delete' in the 'Delete' row. This will delete all records. To only delete the records where archived =yes, add the 'archived' field into the query grid, change the 'Delete' to 'Where' and set the crtieria to "Yes" (without the quotes). This will only delete the records where 'archived' = yes.

If you are uncomfortable with this, create another copy of your data and run a few test delete queries. Once you have confidence with the process, start doing it to your live data.

What you might want to do is to also add this query to your existing button. Make sure the append query runds before the delete query. You can also turn off the warnings so that the users elects not to append the data and then elects to delete the (un)archived data. LAstly, you will probably want to requery your form after the delete query has run - a simple way of doing this is to close the form and then re-open it, again automatically after the append and delete queries have run.

If you are not sure about getting the tasks to run in sequence, post the code behind your button : if you right click the button, click View -> Properties -> Event -> OnClick -> ... click the 3 dots, if it says [Event Procedure] -> this will take you to the Visual Basic screen. Copy and paste the code from Private Sub xxxxx_Click() down to the End Sub part. I can help you modify this if you get stuck.

Lastly, yes you can reverse the process but you will need new queries. Also, rename the 'archive' field in the 2nd table to 'unarchive' and create 2 new queries identical to the 2 you already have (but based on the archived table, looking at the 'unarchived' field) to reverse the process. Be aware that if you change the 'unarchived' field name in your table, then you will need to change it in your archived records form and queries. Again, do your test on sample data first.

HTH, Andrew

 

[claddagh]

Almost Done

Andrew,

Thanks for the info.
I have this now working perfectly, but I am having to execute this using a macro, which i`m not too comfortable with, I would rather have code running instead.
I converted my macro into code, but when I assign this code to my button in my switchboard, it doesnt work.
But the macro does !! strange.
I also have the un-archive working too now, like you said in the reverse process.

Below is the code that access created, but being a vb-less muppet, im not sure what im looking at...

Function mcr_archive_data()
On Error GoTo mcr_archive_data_Err

    DoCmd.SetWarnings False
    DoCmd.Hourglass True
    Beep
    MsgBox "You Are About To ARCHIVE Records From LIVE Customer Data ", vbCritical, "ARCHIVING DATA"
    DoCmd.OpenQuery "qry_archive_data", acViewNormal, acEdit
    Beep
    DoCmd.Hourglass False
    MsgBox "ARCHIVING DATA COMPLETE", vbInformation, "ARCHIVING DATA"


mcr_archive_data_Exit:
    Exit Function

mcr_archive_data_Err:
    MsgBox Error$
    Resume mcr_archive_data_Exit

End Function


The other thing i didn`t understand is the fact that access didnt make any reference in this code to my delete query "qry_delete_archive_cust_data"
which delete the old data, after the data being copied ??????


Thanks for the help.

Max

p.s.  How can I get this to make it say, "ARE YOU SURE" with a "continue" & "cancel" button on there.
There didnt seem to be an option for that in the macro part ??

TIA

 

[andrew93]

Why don't you like using macros?

Modify your code to include the other query by inserting a new line and entering something like this after the archive query line :

DoCmd.OpenQuery "qry_delete_archive_cust_data"

In fact I would probably re-write the main part of the code such that it looked like this (to give you the option to cancel) :

DoCmd.Beep
If MsgBox("Are you sure you want to archive these records", vbOKCancel) = vbCancel Then
 'Edit : If we were to reset the archived = yes back to no (using an update query), then this is where we would do it
 Exit Sub
Else
 DoCmd.SetWarnings False
 DoCmd.Hourglass True
 DoCmd.OpenQuery "qry_archive_data"
 DoCmd.OpenQuery "qry_delete_archive_cust_data"
 DoCmd.Hourglass False
 DoCmd.SetWarnings True
 DoCmd.Beep
 'Edit : If we were to requery the form using DoCmd.Requery, then this is where we would do it
 MsgBox "Archiving Data Complete", vbInformation, "Finished..."
End If

We should probably add a line to requery the form to refresh the data after the archive, and if cancel was pressed, to reset all of the archived = yes fields back to no. See my late notes within the code.

Please note I haven't tested this code.

HTH, Andrew

 

[claddagh]

Works

Andrew,

Thanks for that, it works a treat.
Im not sure about the re-query thing though, as I wouldn`t want the archive but reset to no, even if they cancelled.

I`ll look into that more, and see what the users want.....

Thankyou once again,

Max