Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Location
    Bangalore
    Posts
    15

    Question Unanswered: Database cleanup

    Hi all

    what is the procedure to compress access database.
    My database size is increasing day by day just b'coz i'm inserting and deleting records. I think it is b'coz of the transaction log which it is holding internally unlike SQL server which will have a seperate transaction log and this can be be cleaned up.

    Is this similar to SQL server or does it do something else?
    Anyway i want to decrease the size of the database.

    -Venky

  2. #2
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244

    Re: Database cleanup

    From the database window, go to Tools, Database Utilities, Compact Database.


    Originally posted by venkyt
    Hi all

    what is the procedure to compress access database.
    My database size is increasing day by day just b'coz i'm inserting and deleting records. I think it is b'coz of the transaction log which it is holding internally unlike SQL server which will have a seperate transaction log and this can be be cleaned up.

    Is this similar to SQL server or does it do something else?
    Anyway i want to decrease the size of the database.

    -Venky
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  3. #3
    Join Date
    Aug 2003
    Location
    Bangalore
    Posts
    15

    Re: Database cleanup

    Originally posted by andybriggs
    From the database window, go to Tools, Database Utilities, Compact Database.
    Actually i was looking for something programatically. Sleeping mind...Just woke-up after a T-break.

    Anyway i got the code for it. Here it is, if anyone wants to have a look at it.

    strPathToMDB = "C:\mddb.mdb"
    strTempDB = "C:\Compresseddb.mdb"

    Set objAccess = CreateObject("Access.Application.9")

    objAccess.DbEngine.CompactDatabase strPathToMDB ,strTempDB

    Set objScript= CreateObject("Scripting.FileSystemObject")

    objScript.CopyFile strPathToMDB , strPathToMDB & "z", True
    objScript.CopyFile strTempDB, strPathToMDB, True
    objScript.DeleteFile strTempDB

    Thanks for the help

    -Venky

  4. #4
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    Sorry about the misunderstanding, Venky!! It may interest you to know that there's a freeware product called HAL (Handy Access Launcher) which allows you to schedule the compacting of multiple databases. Can't remember the site right now, but just Google for "Access HAL" and that should find it. It's a very useful utility, but the downside is that it can't be run as a Service - you have to leave the machine logged in. Having said that, I'd thoroughly recommend it.
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

Posting Permissions

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