Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Compressing Database (MS Access 97)

    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.

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Compressing Database (MS Access 97)

    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?
    Regards,

    Gary
    (It's been a while!)

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compressing Database (MS Access 97)

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Compressing Database (MS Access 97)

    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.
    Charlotte

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Compressing Database (MS Access 97)

    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.
    Regards,

    Gary
    (It's been a while!)

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compressing Database (MS Access 97)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •