Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2003
    Posts
    49

    Unanswered: what makes my access database's size large?

    is only the amount of data in the table affect the database's size?
    if i create a lot of queries,forms,reports, do they affect a lot to the size of the database?
    my initial database's size is 8 MB is it normal? or it should be smaller?
    hendra gunadi

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Actually running queries and reports and forms will all artificially enlarge your access DB let alone the actual accumulation of data ... Periodic compacting will reduce the size and increase performance (or at least restore some performance) ...

  3. #3
    Join Date
    Feb 2003
    Location
    Winston-Salem,NC
    Posts
    34

    who says size doesn't matter?

    Ok, this really depends on how much information you have in your db and how you setup your tables but 8 mb seems to be a ok size unless you have very few tables with not so much info in them. Then you may have a problem. Can't really say unless I saw the db, personally I have one with 40 or so thousand records and lots of queries and reports that is about that size. Anywho, queries and reports are only like skeletons, they don't have any weight to them until you run them. This is because they pull information from the tables which acts to fill in the bones so to speak but once you close them they go back to skeleton form. Does that make sense? Anywho, forms are a little different depending on how they are setup, if you have a lot of stuff like bitmap pictures embedded or stuff like that you db will grow very large very quickly. Also if you create a table and fill it up with info then delete it sometime later it doesn't cause your db to shrink in size for some reason, guess there is something stored behind the scenes that maybe someone else knows how to get rid of. Same goes for the forms. I created a blank db and played with it to see how different things affected it and this is what I saw. Hope it helps!

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: who says size doesn't matter?

    Originally posted by bmcgee
    Ok, this really depends on how much information you have in your db and how you setup your tables but 8 mb seems to be a ok size unless you have very few tables with not so much info in them. Then you may have a problem. Can't really say unless I saw the db, personally I have one with 40 or so thousand records and lots of queries and reports that is about that size. Anywho, queries and reports are only like skeletons, they don't have any weight to them until you run them. This is because they pull information from the tables which acts to fill in the bones so to speak but once you close them they go back to skeleton form. Does that make sense? Anywho, forms are a little different depending on how they are setup, if you have a lot of stuff like bitmap pictures embedded or stuff like that you db will grow very large very quickly. Also if you create a table and fill it up with info then delete it sometime later it doesn't cause your db to shrink in size for some reason, guess there is something stored behind the scenes that maybe someone else knows how to get rid of. Same goes for the forms. I created a blank db and played with it to see how different things affected it and this is what I saw. Hope it helps!
    Au contraire my friend ... Running queries and reports and forms in Access takes up room THAT IS NOT RECLAIMED until a compact & repair is run. It is true that queries and forms and reports don't take up alot of room but they do take some ... You are correct in pointing out that the size of the DB in question is determined on how much data is in there currently BUT it is also determined by how many queries reports and forms have been run also.

  5. #5
    Join Date
    Feb 2003
    Location
    Winston-Salem,NC
    Posts
    34

    hmmmm...

    I had copied like 20 of my queries and ran them all and it didn't change the size of my database (well only while I had them open) of course they don't pull large amounts of data or data where the field sizes are that large either, could that be why I didn't notice a change in the size of my db?

    Thanks.

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: hmmmm...

    Originally posted by bmcgee
    I had copied like 20 of my queries and ran them all and it didn't change the size of my database (well only while I had them open) of course they don't pull large amounts of data or data where the field sizes are that large either, could that be why I didn't notice a change in the size of my db?

    Thanks.
    Most likely ... What I've found is that with respect to forms, reports, and queries, Access will allocate a chunk of space and fill it up with the same ... When you add that last report/query/form it will fill over the allocation and then you get another chunk ... From what I've observed it's like 4K increments (which is the min allocation for 2000 NTFS). See?

  7. #7
    Join Date
    Feb 2003
    Location
    Winston-Salem,NC
    Posts
    34

    more ???

    Got to thinking about something else, the access db I give to my employees pulls data from Oracle through ODBC, since most of the data comes solely from the Oracle tables (some from tables use the Oracle data but the information has been slightly modified and put into a new access table) would this cause the variance I am getting? Since the information from the tables really isn't stored in the access db then when I run the queries it isn't increasing the size of the access db?

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: more ???

    Originally posted by bmcgee
    Got to thinking about something else, the access db I give to my employees pulls data from Oracle through ODBC, since most of the data comes solely from the Oracle tables (some from tables use the Oracle data but the information has been slightly modified and put into a new access table) would this cause the variance I am getting? Since the information from the tables really isn't stored in the access db then when I run the queries it isn't increasing the size of the access db?
    Most likely ... The same applies for queries. When you 1st run the query, there is an permanent allocation that is used for susequent runs of the same query ... If there is no increase (at least past the allocation) then there would be no corresponding size increase of the DB ...

  9. #9
    Join Date
    Feb 2003
    Location
    Winston-Salem,NC
    Posts
    34

    ...

    Cool, learn something everyday!

    Thanks!

Posting Permissions

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