When you say "... ACCDB Master file which I use to make updates to the overall file", do you mean updates to the ACCDE tables and other objects, or to the Backend tables? Or both?

The ACCDB file is for making updates to the front end database which my users will use. I guess what Im trying to figure out from watching the videos is how to archive data from my backend tables once they reach a certain age. We normally keep records for 7 years in my agency so I assume each relevant table in the backend would need its own archive? Each archive database would be used to hold records from on year to 7 years old where the oldest records at the beginning of each year (those aging up to 8 years old) would be purged from the archives making room for newer records... Im sorry if this is getting confusing to read, its getting confusing to explain... Guess it's pretty evident I am in over my head here at this point huh?

@Daniel This old-school guy is used to backups being very organized. When I archived it was to speed up record retrieval in a table. Most production files weren't purged as we could just keep throwing more and bigger hard drives at the data. So, I had 25 years of data that blew clients away come analysis time. That isn't an option with ACE's limitations.

Anyways, I think I watched Richard's video a few years ago. So, I'll have to view it again to get up to speed.

Just an idea, since I've found purging just some data will cause problems in the future, because of all the related records. A big project to organize correctly. Have you considered doing a yearly backup of the backend and the ACCDB at the same time? Then, when data needs to be reviewed you just fire up the old database. You may find it cumbersome to merge the data with your new backend data as tables often get tweaked over time, but at least you would have a working frontend & backend to figure out what's changed and how it used to work with ALL the data. Just a note, if a table design does change, even the slightest, then simple importing won't work.  Even if you do end up "purging" you'll probably like my scheme too some day.

@Thomas I think I understand what you are saying. create a backup of both the FE and BE database files at the end of each year, and start anew at the beginning of the year with a blank database?

@Daniel Nooo to the starting anew. Just keep copies of the two (FE & BE), in a very organized manner. That way you can see what was the db state going back to any year. This is a good practice even if you don't modify tables and other objects. It was quite common for small corruptions to sneak into a database, causing data loss or weird data. Since I ran financial applications with billions of dollars, balanced to the penny, it was important to be able to research those corruptions that might appear in data that was years old. After the save, you can purge.

The question then becomes to purge or archive for specific data. If the data needs to be referenced with frequency, the norm is to archive, so that it can be referenced without going to the backups. How you're going to reference it becomes complicated if your table design gets modified after archiving, as mentioned earlier. At the very least, you can inspect an old table.

If you don't reference the data regularly, then a purge may be more appropriate. But, you have to be careful to eliminate all the referential data to maintain integrity in the db. Also, you may need to plan a way to find the data without going through umpteen year-end backups. Having an abbreviated table to help find archived data comes in handy. I'll give an example.

I tracked advertising claims (similar to your medical claims with your insurance company, same concept generally referred to as claim processing). As I stated earlier, I didn't purge data, but I did relocate it to keep the primary table of smaller size (since at first, our old db system didn't have built-in indexes). When I designed the application, I did plan for purging (since I had no idea that hard-disk sizes would grow much faster than my data).

So, what I planned, and will plan for in my ADS applications (thanks to the 2GB limitation in ACE), is a way to purge, but leave a bread-crumb trail behind. Specifically, a manner of flagging in the highest-level table for a hierarchy of tables, that the subordinate data has been purged. In my claims example I would keep the claims table data intact, adding just a purge date. Then all the massive amount of transaction data would be purged for each claim. That way, if a client called in about a specific claim, I could find that we did process it many years ago and for how much $, but that it's been purged.

@Daniel I finished Richard's Archive video. He did a great job explaining the basics and some of the gotchas. However, he mentioned split database, and then seems to bypass that whole consideration. So, the viewer is left wondering how to handle it for a FE/BE situation. Richard often supports the view of some developers that users should never be in the BE. I go with the view that a data administrator can do work in the BE, to speed up things, reduce network load and keep "jobs" out of the hands of ordinary users (especially if the menus don't use permissions/passwords to run jobs).

For me:
1) I never allow modifications to archived data.
2) I don't do the trick of toggling between tables in a form because in my more complicated hierarchy of tables that would be quite a task, and any research often required pulling source documents from storage to answer a question.
3) I put certain "jobs" (like archiving) in the BE database for the data administrator, so the BE isn't just tables.

@Thomas I appreciate your dedication to assistance in this. I think my brain got overloaded yesterday with all of this. I have opened up my BE database and see that the tables are not linked in the way they are in the FE. I am thinking of making an archive database from the BE where there is a form on the archive database that allows the database administrator (me for now, others as time marches forward, and jobs change) to do the archiving of the data to the table in the archive database. Thoughts?

Daniel

Backend tables are NOT supposed to be linked. They live in the BE. Front end should have its tables only as Lined tables to the FE.

Sami

Would it then be possible to make the archive database work from the BE database?

Yes, but...

I agree with Richard that you should keep all your programs in your front end and your data in the back end. This will save you from future problems.

@Daniel, Richard has agreed with me, in some post somewhere, that there's no reason not to do work in the BE, it's just that some think that isn't appropriate. I gave the reason for my doing so. There's no right or wrong way, I just happen to think it's better to do some tasks directly in the BE. It's a bit more work in that you need to put applications in the BE and have a way to maintain them different from the FE, but it's not that complicated.

There's also no reason not to have linked tables in the BE and some unlinked tables in the FE. I do it, again for good reasons.
I can't imagine what future problems Sami is referring to, but I'm happy to be enlightened.

I did a lot of testing of my software with huge VBA code running in both the BE and FE. The difference can be minutes vs. hours. The code worked fine, either way. Obviously, the FE method brought the network to its knees.

@Thomas, I went ahead and made an archive of my BE and set it up so that it can't be accessed by regular users, only ones with matching data in the TempVars set up in the logon form. Yes, it has the same logon form used in the FE so that it protects the data from being interfered with for the most part. Once the user successfully logs in, they can enter a date for archiving the data and then click a button to archive a selected table (Unit/Center Reviews for example) (Yes, I know... spacing in the table name...) Once that data reaches the age for purging, I have a separate form to with buttons to perform that task as well. SO FAR it appears to be working correctly. I am now on to showing my users when archived data exists as Richard does in the Members Only video. Next, its on to performing an automatic backup and an automatic compact and repair... Many questions to follow on those I am sure. But that is all for another day! Thank you all so much for your help!

@Daniel I'm happy to give my two cents and that you got it to work.

I haven't gotten to automatic backing up of the BE. I suspect it's going to be complicated, in that to do it properly, as I understand, one has to open the BE exclusively, which keeps users from accessing the data. I played with it sometime back, and don't remember all the implications and results.

Maybe Richard has a seminar/Tech Help on this?

@Thomas, Yes, Richard has a Tech Help video on backing up a database. I was watching it earlier and got lost several times so I gave up for the day.