MS Access- File Size increasing dramatically

Discussion in 'Archived Threads 2001-2004' started by Todd Hochard, Jul 1, 2002.

  1. Todd Hochard

    Todd Hochard Cinematographer

    Joined:
    Jan 24, 1999
    Messages:
    2,312
    Likes Received:
    0
    Trophy Points:
    0
    All,

    I set up a simple database using Access 97. It's a couple of tables, a few forms, and some queries. Upon completion, entry of about 60 records, and compacting, the file size was 312KB.
    I attempt to add ONE record, with a total of maybe 500 BYTES of characters, and file size jumped to 500+KB.

    The reason I bring this up, is that the user added about 5 records, maybe 1500 characters total, and the file size is up to 1708KB. This wouldn't have anything to do with Field Size limits that I put into the tables, would it.

    I haven't had a chance to get into it yet, but I thought I'd put the feelers out. Any ideas?

    Todd
     
  2. Shayne Lebrun

    Shayne Lebrun Screenwriter

    Joined:
    Jun 17, 1999
    Messages:
    1,086
    Likes Received:
    0
    Trophy Points:
    0
    Compact it. Tools->Database Utilities->Compact and Repair, IIRC, in Access2K. Dig around and you'll find it.
     
  3. Todd Hochard

    Todd Hochard Cinematographer

    Joined:
    Jan 24, 1999
    Messages:
    2,312
    Likes Received:
    0
    Trophy Points:
    0
    Well, I figured that Compact would fix it, and it did. The 1708KB file dropped to 468KB with compacting.

    Which is all fine, BUT-

    This doesn't help me much. The DB was supposed to be plug and play, switchboard-driven, so that the user didn't have to be burdened with this (as she's not very computer savvy). So, is there something in the way I've constructed the file to cause this?

    I've searched the web, but not much found, other than the decompile function. I may try that.

    Todd
     
  4. Chu Gai

    Chu Gai Lead Actor

    Joined:
    Jun 29, 2001
    Messages:
    7,270
    Likes Received:
    1
    Trophy Points:
    0
    myself i'm an access 97 user but i believe the latest incarnations of access allow for compacting to be performed by the database in a user transparent fashion. now if you have any pictures in the database, that will accelerate the database bloat.
    forget about searching the web for information. the best places to post questions such as you have are on the newsgroups.
    for example one has:
    comp.databases.ms-access
    microsoft.public.access.forms
    microsoft.public.access.macros
    you get the idea. while newsgroups can be accessed off of the web or through Outlook or Outlook Express, I've found it preferable to use Newsgroup Reader programs. Gravity and Agent come to mind. There's a fully functional and quite good, free version called aptly enough, Free Agent...that can be found at www.forteinc.com i believe.
     
  5. Shayne Lebrun

    Shayne Lebrun Screenwriter

    Joined:
    Jun 17, 1999
    Messages:
    1,086
    Likes Received:
    0
    Trophy Points:
    0
    Access is just doing it's job.

    For example, when you 'delete' a record, Access is probably simply marking it as 'deleted.' And so on, and so forth.

    All ye need do is script the compact to happen as appropriate.
     
  6. Todd Hochard

    Todd Hochard Cinematographer

    Joined:
    Jan 24, 1999
    Messages:
    2,312
    Likes Received:
    0
    Trophy Points:
    0
     
  7. Shayne Lebrun

    Shayne Lebrun Screenwriter

    Joined:
    Jun 17, 1999
    Messages:
    1,086
    Likes Received:
    0
    Trophy Points:
    0
    Ok, you're the boss. I'm not going to argue with you. Lord knows in my five or six years of database and network administration, I've not picked up a thing or two. :)
     
  8. Todd Hochard

    Todd Hochard Cinematographer

    Joined:
    Jan 24, 1999
    Messages:
    2,312
    Likes Received:
    0
    Trophy Points:
    0
    Shayne,
    I don't doubt, for a second, that you are far more knowledgeable than I re: databases. This is still the realm of "hobby" for me.
    HOWEVER, "that's just the way it is" just doesn't float. As I said, of the five databases I've constructed, this is the only one that insta-bloats when I put in ONE record (again, 500 characters of text, total). It just doesn't make any sense.
    The others are similarly constructed (but larger, with thousands of records, versus 65 or so), with similar (but more complex) switchboards, queries, forms, etc.
    As a professional troubleshooter, regardless of database knowledge, I can recognize when "one of these things is not like the other..."[​IMG] I must be missing something...
    The big trouble is that with Access2000 (on my machine), I don't see the same issue. And, I won't be able to get to the other machine until late in the week.
    (Just to be clear, it bloats every time a record is added, not just prior to the first compact.)
    Todd
     
  9. Shayne Lebrun

    Shayne Lebrun Screenwriter

    Joined:
    Jun 17, 1999
    Messages:
    1,086
    Likes Received:
    0
    Trophy Points:
    0
    Hrm. Is this database something I could take a look at? Sounds like you're interested in the 'why the hell?'s as opposed to the 'is this my fault's. :)

    If I can take a look at it, I might be able to render a better answer for you. Otherwise, all I can do is start reeling off possibilities. :)
     
  10. Todd Hochard

    Todd Hochard Cinematographer

    Joined:
    Jan 24, 1999
    Messages:
    2,312
    Likes Received:
    0
    Trophy Points:
    0
     
  11. Kolya

    Kolya Stunt Coordinator

    Joined:
    Mar 10, 2001
    Messages:
    91
    Likes Received:
    0
    Trophy Points:
    0
     
  12. Shayne Lebrun

    Shayne Lebrun Screenwriter

    Joined:
    Jun 17, 1999
    Messages:
    1,086
    Likes Received:
    0
    Trophy Points:
    0
    No, it's not a jab at all. It's a simple observation. In my experience, there's two kinds of people, when it comes to something not working how they expect it to. The first kind says 'What the hell?' and basically want to be reassured that it's not their 'fault' or doing or whatever, and then are happy.

    The second kind says 'Why the hell....?' as in 'why the hell would it do that?' and wants to know the deeper darker secrets. That would seem to be where you are at the moment, so I offered to take a look at your specific database to see if I could offer some insights that are specific to your situation.

    I apologize if you took offence to something I said, for none was offered.
     
  13. Todd Hochard

    Todd Hochard Cinematographer

    Joined:
    Jan 24, 1999
    Messages:
    2,312
    Likes Received:
    0
    Trophy Points:
    0
    Then, I apologize for jumping the gun.
    I definitely fall into the 2nd category. I like to get to the bottom of everything.
    New problem- I can't get it to bloat anymore. The file size increases about 14K on my Access2000/WinXP setup at home after adding a few records. Hardly a problem. I'll try it at work on our Win98/Access97 machine, and see if I can duplicate the symptoms.
    I think the real problem is that my user has an e-Machine. I HATE those computers![​IMG]
    Todd
     

Share This Page