Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Mar 2002
    Location
    Australia
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    growing size of mdb file (Access2000)

    I m new to Access. I tried to convert an old Access table with 6k + records to three new tables. I created a new mdb file w/ the new tables and then imported the old table into this file. I used VB/sql to move all the data from the old one to relevant new tables. The original size of the mdb w/old table is abt 800KB. I didnt check what size of the new mdb file after created the three new empty tables. Now I surpisedly find that the new mdb file with very large size of 46 MB after finished the data transfer. In the process of doing the transfer, I did deleted and reimported that old talbe twice, and deleted contents of some new tables sereral times to try my codes, methods, etc. I didnt add any new record. For the three new, one is w/ same number of records as the old one but 6 columns (11 cols in the old); 2nd has only two col and one is fully empty, w/ abt 4800 records; 3rd one is the smallest, w/ 5 cols (2 empty) of 27 records. The 3rd table uses auto number for PK and I did deleted the contents many times, so pk number now reaches at abt 400 (table size is 27 records). I doubt very much the 46MB is the proper size for the three new tables comparing to the old one w/ 800KB.
    In fact the 46MB remains the same before and after I deleted the old table from this new mdb file. It looks to me the file size keeps growing without cleaning the unused/deleted space. Can anyone tell me how to deal this problem? I m supposed to carry the file in a floppy disk, well....

    Thanks
    David

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: growing size of mdb file (Access2000)

    Have you compact and repair the database ?
    If this don't reduce the size enough, try to create a new empty database and import all the object from the 'big' database.
    Be aware that if this was a conversion from Access 97 to Access 2000 the size will grow as Access 2000 is Unicode compatible and every character of a text or memo field is stored in two bytes instead of one in Access 97.
    Francois

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: growing size of mdb file (Access2000)

    What I found when I first used Access 2000 was that when you imported data into tables it would grow to an extraordinary size. Microsoft KB has a few examples of Database Bloat as they put it.
    HTH
    Pat

  4. #4
    Lounger
    Join Date
    Mar 2002
    Location
    Australia
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: growing size of mdb file (Access2000)

    Thanks Francois,

    I created a new mdb file as you suggested and imported all the three tables in. I checked the content of all the tables. They are the same content. Now the size down from 46MB to ---- 900KB ----!!! Is it great? Thanks a lot!
    By the way, I dont know what you meant "compact and repair the database". I guess you didnt talk abt zip file. So how to compact/repair access db? Another question may sound silly: I dont know how to change the file name by Access. I can only change the name from Win Explorer now. How can I do it in Access?

    David

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: growing size of mdb file (Access2000)

    When you are working in an access database, access can create temporary tables or queries. This make the file growing. When they temporary data isn't needed any more access mark this space as available. Also when you delete data, this space is marked as available. The file never decrease. It can only grow. Therefore there is the Compact and repair instruction.
    When in the database window, select the menu Tools, Database Utilities, Compact and Repair Database.
    For renaming a file you can use the instruction Name in Visual Basic. Here the samples of the help file. Pay attention that this is for any file that is not open. As you database is open, you could not rename it from in itself. You have to do this from Windows Explorer.
    Dim OldName, NewName
    OldName = "OLDFILE": NewName = "NEWFILE" ' Define file names.
    Name OldName As NewName ' Rename file.

    OldName = "C:MYDIROLDFILE": NewName = "C:YOURDIRNEWFILE"
    Name OldName As NewName ' Move and rename file.
    Francois

  6. #6
    Lounger
    Join Date
    Mar 2002
    Location
    Australia
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: growing size of mdb file (Access2000)

    Yes, I tried to use Compact and repair function as you suggested. It is so easy to compact the size down. The same file by using this way is reduced to 1076KB, a little bit more than import method, but in the same significance to cut size from huge 46,000KB down and much easier. I cleaned up several other Access dbs by this way too.

    Thank you so much (also for your help on name change)!!
    david

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: growing size of mdb file (Access2000)

    Also, look into Decompiling the database. This is quite effective if you have a lot of code in your db.

    See this post.

    HTH <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

Posting Permissions

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