Archiving large database 2

[cavery]

Hello folks,
I'm managing a very large database that contains thousands of records. I heard there is a way to automatically archive data; by using a macro or by creating an append or delete query? I basically want to archive any data that isnt being used. Such as old records from 4 years, but I still want the user to have access to this data. Any suggestions on how I can Archive my database?

Thanks,
Clark
Honda of America,

 

[GDGarth]

Here's what I do:

Make a copy of your data table to hold archived data (I make mine a linked table, to keep my database under the maximum size limit). Also, make a copy of the database and put it in a safe place, in case you delete data by mistake while you're setting this up.

Create a query listing all the fields in your data table. The easiest way to do this it to use the Query Wizard. Then open the query in design view. In the &quot;Date&quot; field (I'm assuming you've got a field with a date in it) criteria field, enter &quot;<Now()-365&quot; (or however many days you want to keep in your main data table).

If you look at the records in your query now, you should only see records from the past year.

Click on the query tab in design mode, and select &quot;Append Query&quot;. When prompted for the table name, choose your archive table name. Now, when you run the query, it will add the records to the archive table.

Now create another query, with exactly the same criteria. Click on the query tab, and select &quot;Delete Query&quot;. Now, when you run this query, it will delete the same records it appends to the other table.

You can automate the process by calling the two queries from another procedure, or having a command button to start it (or you can use a macro). To do it with VBA, put this code in the on click event of a command button:

docmd.setwarnings false
docmd.openquery &quot;Append Query&quot;
docmd.openquery &quot;Delete Query&quot;
docmd.setwarnings true


Make sure to run the queries in this order, or you'll delete before you append. Your users should be able to access the data from the archive table (you may have to set up queries or forms for them, of course).

 

[cavery]

Thanks, so far I've tried and keep getting error message: Datatype mismatch in criteria expression
I placed the date in the criteria field as stated with your code......I believe there could be a problem with the name of my table.

Any suggestions?

Thanks,
Clark

 

[GDGarth]

Is your date field really a date, or a text field that looks like a date? That's my best guess at what the problem is. You didn't put the quotation marks around the expression, did you. You should have

<Now()-365

in the criteria field.

To test it, put a literal date in the criteria field. If it takes that, it should take the expression.

If your date field is actually a text field, you'll need to make your criteria a string.

If your date field is a date field and you can't get the expression to work, put this in the criteria field:

<[Enter Date]


Then, you'll be prompted for the date, and it will do everything earlier than the date you input.

Keep trying, you'll get it to work.

 

[cavery]

Thanks, it works like a charm.....stars!
Do you have any suggestions on how I can give users access to retrieve archive data?....besides me telling them to go into the database?.....Thanks,
Clark

 

[GDGarth]

If you want a really easy way, just make a copy of your database and name it something like &quot;OldData.mdb&quot;. Put your archived table in it instead of the new date (rename it to the original data table).

Then, your users can access the data just by opening up &quot;OldData&quot;, and use your existing queries, forms, and reports to access the data.

This solution works OK if your users need only occasional access to the old data.

Another alternative is to create forms and reports just to access the old data. That's pretty easy, if you just copy the existing ones and change the datasource property.

If you're really handy, the most elequent solution is to use your existing forms and reports, and switch the data source according to the user's preference. That's possible to do, but it's a lot more work.

I usually just make a copy of the database. Most of the time, the archived data isn't needed too often - if at all. I've got a database that keeps two years worth of test records (about 500,000 records). When I decided to archive the old data to keep the database to a reasonable size, I had a couple of people get real wound up, saying they needed the old data. I went to a lot of work setting up a duplicate mdb, and it's NEVER been accessed! Next time, I'll just archive the data and wait for someone to ask for it.

 

[cavery]

For the first idea, (which I like, since the data would be use periodically) would I still need to create new forms, reports, or basically use the old forms? reports?....

Thanks,
Clark

 

[GDGarth]

You can use your existing objects. You just need to point them to the correct table.

If, for example, you've got you database split into front end and back end, make a copy of your back end, and put the archived data in there instead of the new data. Make a copy of your front end, and change the link to the back end to point to the database with the archived data.

If you don't have the database split, you can just make a copy of the database, and it should work OK.

This sounds a lot more confusing than it really is. If you're used to working with linked tables, it's a snap.