Compress Access database using VBA

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Narayanan G V
    New Member
    • Dec 2006
    • 1

    #1

    Compress Access database using VBA

    Hi...,

    I am facing a problem with the Access database file once the size reaches 2GB (Access 2K)... Is there a VBA code which can enable the file to compress itself automatically when I complete running a module...

    Thanks,
    Narayanan
  • missinglinq
    Recognized Expert Specialist
    • Nov 2006
    • 3533

    #2
    You can try compacting the db. If your app is truly bumping the 2GB limit, you need to split it into FE/BE and move the backend to SQL Server or
    My SQL or some such.

    Comment

    • AricC
      Recognized Expert Top Contributor
      • Oct 2006
      • 1885

      #3
      I'm not an expert with Access, but this is some VBA that may help I've also seen some scripts that will auto compact your DB every 8 or so times it is open. Like stated above, if your DB is that big you may also want to consider a different DB solution or archive some of the items in your current DB.

      HTH,
      Aric

      Comment

      • ADezii
        Recognized Expert Expert
        • Apr 2006
        • 8834

        #4
        Originally posted by Narayanan G V
        Hi...,

        I am facing a problem with the Access database file once the size reaches 2GB (Access 2K)... Is there a VBA code which can enable the file to compress itself automatically when I complete running a module...

        Thanks,
        Narayanan
        Have you considered Tools, Options, General, Compact on Close. Selecting this
        Option will automatically Compact the Database every time it is closed. Hope this helps..

        Comment

        • NeoPa
          Recognized Expert Moderator MVP
          • Oct 2006
          • 32669

          #5
          Unfortunately,
          ...Access 2K onwards seems to be a bit crappy with its space usage.
          ...Anything anywhere near 2GB will take ages to compact.
          ...When a db gets anywhere close to the limit it is unable to execute various tasks without crashing.

          All things to bear in mind.
          You can split the back-end database into multiples or consider a different solution (SQL server etc).
          Using a db that regularly reaches that sort of size is trouble.

          Comment

          • Killer42
            Recognized Expert Expert
            • Oct 2006
            • 8429

            #6
            Originally posted by NeoPa
            Unfortunately,
            ...Access 2K onwards seems to be a bit crappy with its space usage.
            ...Anything anywhere near 2GB will take ages to compact.
            ...When a db gets anywhere close to the limit it is unable to execute various tasks without crashing.

            All things to bear in mind.
            You can split the back-end database into multiples or consider a different solution (SQL server etc).
            Using a db that regularly reaches that sort of size is trouble.
            Hahaha... tell me about it! :)

            A couple more things to keep in mind.
            • NeoPa, you recently posted a routine to do the compacting - how about dropping a copy (or link) here?
            • In my experience, although a close-to-2GB database may take some time to compact, it depends on what's in it. The more indexes, the longer it will take. And of course it depends heavily on the hardware. My work PC takes a few minutes.
            • There are lots of performance considerations when splitting you database into front- and back-end. But I expect you can make significant gains if the back-end goes into a "real" databsae (SQL Server, etc.). I didn't have that option. Splitting and using Access for both may be counter-productive. On the other hand, it may be possible to overcome this with some tweaking.
            • If you do split, you might save yourself some headaches by having any frequently-updated data in a separate database to static data, so the static data never has to be compacted. Database splitting is a complex subject though, and I'm certainly no expert in the area.
            • Have you looked into ways to reduce the size? For example, I was able to make some good gains by extracting repetitive text values to a lookup table. If you don't have Unicode compression on, I hear this can also make a big difference.

            Comment

            • Zerin
              New Member
              • Dec 2006
              • 56

              #7
              Dear members,

              Got some information about Compact DB.You can go for:

              http://support.microsoft.com/default...b;en-us;306287


              Best regards,

              Zerin

              Comment

              • Zerin
                New Member
                • Dec 2006
                • 56

                #8
                Originally posted by Narayanan G V
                Hi...,

                I am facing a problem with the Access database file once the size reaches 2GB (Access 2K)... Is there a VBA code which can enable the file to compress itself automatically when I complete running a module...

                Thanks,
                Narayanan

                Dear Narayanan G V ,

                I am suffering by the same problem also and decided to switch my database into SQL Server.If possible, you can do it too.

                Or just learn socket programming and threading by VB.After you finish learning, I think you'll get the idea.Other than that........... ..............! ? I really don't know. May be exparts from the forum can help you.

                Zerin

                Comment

                • Zerin
                  New Member
                  • Dec 2006
                  • 56

                  #9
                  Originally posted by Narayanan G V
                  Hi...,

                  I am facing a problem with the Access database file once the size reaches 2GB (Access 2K)... Is there a VBA code which can enable the file to compress itself automatically when I complete running a module...

                  Thanks,
                  Narayanan

                  Hello,

                  You can read this also: http://www.rondebruin.nl/zip.htm

                  May be you can get some interesting information.

                  Zerin

                  Comment

                  Working...