Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2

    Question Unanswered: Database Size Oddity

    I have noticed that the size of my database has shrunk rather dramatically. I have been working on it a bit adding and adjusting code and during the last round of maintenence the database went from 23MB to just under 8MB.

    Fearing the worst, data loss, I spent 2 days auditing the records to make sure everything was still there, which, thankfully,it was.

    Can anyone explain what may have happened here? Has this been seen before?

    I am using Access 2003.

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Yes, it happens when yo make a "Compact and Repair Database".

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    to expand on MStef-ZG's answer
    In normal use Access, especially when making changes to forms, reports, tables and so on Access 'bloats' in file size .I think it makes copies of the original objects to work on, but doesn't release space back to the operating system. So when developing, and before final release you should always compact and repair the DB to reclaim that free space. bitter cynics like myself would always take a copy of the db prior to doing a compact & repair just in case it goes wrong
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    That's sor of what I thought. I have never actually used compact/repair prior to distribution, but will do so from now on.

    Thanks

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    As healdem suggested, make a backup copy prior to doing the C & R! Things can and do go awry during the operation, which is why, IMHO, the "Compact on Close" option should never be utilized!

    And "no," developers with common sense make a copy of the db prior to doing a compact & repair! Bitter cynics make two or three copies prior to doing a Compact & Repair!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    May 2010
    Posts
    601
    I would also recommend that you split your database.

    IMHO, this also adds another layer of protection for your data. How is this? Several ways. Here are a few:

    1) When you forget to make a back up and you run the compact and repair and it corrupts your database. You will not lose your data in the back end.

    2) You can develop and properly test by using a copy of your data (back end). When you are ready to deploy you relink to the production/live data.

    I am sure you would never do this:
    While testing you run an update or delete query and the WHERE condition was missing or incorrect. Oops ... now you just deleted all your data or updated all the records when it should have been just a few. Not a problem if you are using test data (back end).



    This is why I start every database split. Even if for a single user and/or a stand alone PC.
    Last edited by HiTechCoach; 12-07-10 at 17:25.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Boyd's advice on splitting every database is well worth heeding, and absolutely, positively a must do for multi-user apps!

    Being in forced retirement, I spend about 6 hours a day on this and four or five other Access forums, and have done so for the past four years. The horror stories about corruption, secondary to running non-split, multi-user databases, are legion! The really bad thing is that these apps can run, unsplit, for years without problems! And then some unknown confluence of events occurs and splat! Suddenly your data is useless! And once this problem occurs it tends to become chronic, unless the app is split. In the past month I have seen reports of this happening to two different databases, one after running without problems for ten years, and one running successfully for an incredible fourteen years!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Amazing how such a seemingly innocent question generates so much discussion. Splitting the database is something I never thought and have never even been instructed on in the couple of courses I have done! I'll look in to it immediately. Our database has been up and running, without problems, since 2002, but I am fastidious about backups and only working on copies of the original.

    Thanks for the heads up guys.

  9. #9
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by sheusz View Post
    Amazing how such a seemingly innocent question generates so much discussion. Splitting the database is something I never thought and have never even been instructed on in the couple of courses I have done! I'll look in to it immediately. Our database has been up and running, without problems, since 2002, but I am fastidious about backups and only working on copies of the original.

    Thanks for the heads up guys.
    Curious, how do you deploy your changes?

    This might help:
    Splitting your Access database into application and data
    Last edited by HiTechCoach; 12-08-10 at 00:15.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  10. #10
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Boyd,

    Changes have been (until now) few and far between so I've been manually updating the databases at our offices. There are only 3 and it doesn't take long. I'll take the database off line, make backups, make changes then bring it back on line.

    I am reading & re-reading your link to splitting a database. Question. I split an old copy and lost forms, queries & reports. data was intact. Does this mean that I'll eed to re-inject these objects in to the split database. I also can't see where the back-end data has been stored.

    Ignore last question. Dumb attack!
    Last edited by sheusz; 12-07-10 at 21:35.

  11. #11
    Join Date
    May 2010
    Posts
    601
    Did you find your forms, etc in the front end and just the tables in the back end?
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  12. #12
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Found them thanks. Me just not reading thoroughly enough. I am having some issues with some queries but I should be able to sort those out.

    Thanks.

  13. #13
    Join Date
    May 2010
    Posts
    601
    That is great.

    You will also need to learn about using the link table manager.

    1) When you change the back end's location you can use the to link table manager to relink the table to the new location.

    2) When you want to swap between test data and live/production data you will use the link table manger to switch between different back ends.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  14. #14
    Join Date
    Jun 2009
    Posts
    48
    On a multi-user unsplit database how are the records kept in synch? If user A changes, adds or deletes a record, the other users' record set is now different.

    Charles

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    split DB means
    the data is in one MDB/MDE
    the application (the front end.. the forms, reports and so on) is in another mdb, prefereably MDE
    ideally you'd want each user with a separate front end , ideally ion their own workstation, all talking to the same common back end datastore

    having a single mdb/mde, ie not split into front & back end is asking for trouble,, and if the fornt end crashes then it can take out the data as well. soemthign thats a lot harder to do if you have split the the db in the first place.. doersn't mean you can't trash the backend.. just that you have to work harder at screwing things up and you have to be very unlucky

    to maintain the front end there is some nifty code provided by PKStormy in the code bank to make certain the user is using the correct (up todate) front end
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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