Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004

    Unanswered: Visual basic Access database size changes

    Hello. I have a couple of questions hopefully someone can easily answer. You probably don't even have to know any visual basic, but I'm reading a database using Microsoft ActiveX data Objects 2.8 (ADO) in visual basic.
    here's what my connection to the database looks like:
    myDBConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & myPath & ";"

    here's what my recordset opens look like:
    rs.Open query, myDBConn, adOpenForwardOnly, adLockReadOnly, adCmdTxt

    I've figured out the size is changing unnecessarily and when I compact it, it will go back to the correct size again. I can't open the database tables and see any differences. So there's this unknown data (32 KB worth) being put in there somewhere.

    1. Is there any possible way for the size to change on the database using read only recordset opens?
    2. Why is the ldb file still being generated even though I'm only doing read only queries and I'm not changing anything?
    3. How does the size of a database change even though you're not changing any data? (ie .. why was it necessary for Microsoft to make a "compact" tool in the database... what is going on in there to make it expand with unnecessary data in the first place?!)

    If someone can answer those or give me links to a tutorial on how the access jet engine works with databases and deals with objects, and lock files that would be nice too. Thanks!

  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland
    in a testdb: opening formA does nothing to db size; opening formB adds a few dozen kB.
    both forms have no code in the _Load() event and have no recordsource.
    formA is default everything, formB differs from the default (backcolor, font, fontsize, etc etc). moral of the story: almost anything you do increases the db size.

    life is too short to worry about this stuff!
    set for compact on close and forget it.

    1. yes
    2. opening the db creates the .LDB
    3. "why was it necessary for Microsoft" hmmmm

    currently using SS 2008R2

  3. #3
    Join Date
    Jul 2004
    To expand on izy's comment, you should get a life and don't ponder MS Access design issues, especially when the increase is 32KB!!! Worry when your database goes from 15MB to 100MB or more.

    Compacting is necessary because Access duplicates itself within its shell (It is a shell), the objects (tables and queries) when altering and adding data and structure. This is a protection scheme in case of a crash and recovery is required. Have you ever looked at an Oracle light database size???

  4. #4
    Join Date
    Aug 2004
    thanks! actually inew it's not the 32kb of space i'm worried about, that would be ridiculous... i'm just worried about any change in size because it will affect the way program works with updating local db's...

  5. #5
    Join Date
    Jul 2004
    If you are using several unlinked (standalone) databases on the same workstation, then each expands on its own and independently of one another. If it's a linked arrangement with Front end/Back end, then only the back end (data) is worth monitoring and only if there are massive changes like additions and/or deletions of thousands of records. Howewever, once the work rythm is established, then so will the interval of the compact repair procedure. Database size can change during design, but this settles once design ends. To test expansion of a Front end database, compile it into an .mde database and then the change is negligible or none.

Posting Permissions

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