Compressing Database (MS Access 97)

[dreyes]

I have a database that is about 38mb. So for I have about 2 years worth of data in it. I have now been told that i am to keep only 1 years worth of data and that each month I need to delete or remove and save the old data someplace esle. Then I have to run compress on the new database.

I understand about running the compression after I have removed the data. BUT, what does deleteing older (by 1 month) data do for me? Management thinks that by deleting data it will help the database run faster, smoother and cause less problems. My concern is that by deleteing data each month and running compresson so often this might cause problems.

[GARYPSWANSON]

Daniel,

Reducing data is one way of helping to speed processes however, if your database is only 38MB and you are having problems with the speed, then perhaps you should review the database design and processes to see how they could be improved. We maintain several databases that on the average are about 500MB based on exchanging data with other systems. We have also re-designed the databases by staging data when data loads so it is already pre-processed as opposed to processing each time a set of queries are run. (This has increased the performance of the database significantly). As we constantly delete old data and have the databases compact on close, I don't see how deleting data or compacting the database would make your database worse.

The bottom line is that if your database design is solid (i.e., system architecture, database structure, programming, etc.), then deleting data and compacting the db should not negatively effect the db. Actually, I have found, that if the databases are not compressed regularly, then problems start occurring.

Also note that rather then delete the data from the database, you might want to archive it in another table so it is readily available for use if needed.

What kind of problems are occurring with the database?

[dreyes]

We use MS Outlook forms as a front end to the database. 99% of the work is for the form to enter data into the database. The 1% is for the reports that I run at the end of the month.

Last week (Friday of course), the database was corrupted - that is two tables were corrupted. I was able to fix the problem simply by running the utilities Repair and Compress. Now, management thinks we have to much data and that each month we have to remove/archive so that only 1 year is active. As for the speed, will there's not much you can do to speed up someone entering data to a form. There are no calculations, queries done. Just simple data entry.

Just to make this more interesting, I have now been told that I need to see about converting my Database into one of the following: MS Access 2003, SQL, DB2(Mainframe). Also to look into VB.NET, WEBDAV - Storage Procedures.

[charlotte]

Your management doesn't know what it's talking about. Using Outlook as a front end is where most of your problems lie, and mucking around with the data won't cure that. If the database is A97, look at converting it to 2003. The others would be total overkill for a 38Mb database, but someone has apparently heard that when you have problems with "Access", you may need a "real" database. In this case, that is nonsense. As for Dot Net, etc., you're still looking at storing data somehow. Yes, you can use XML to store the data, but XML is just unsecured text files. It's up to you to find a viable method and propose that in order to short circuit their meddling in something they don't understand.

[GARYPSWANSON]

Daniel,

Charlotte's response is excellent. When our databases do get corrupted, 99% of the time we can link it to our network having problems (i.e., hiccups where we lose the connection briefly) while data entry is occurring. This causes all kinds of grief including corrupted databases, locked databases, etc. I probably use the JetComp utility at least one a month to unlock a "stuck" database after finding our network has gone down. Good luck in getting your problems resolved.

[dreyes]

Thank you. It seems that I was thinking along the same lines.