File Length - Compact

[VivDenham]

Hi there
I have a MS Access 2007 database which frequently grows to over 2Gb. I want to be warned when the file length gets to, say, 1.5Gb. I have got so far with the following code - please bear in mind that I am an absolute beginner!!! - but don't know where to put this. Do I put it in a Form, or in every form, or on the Start Up form, or where?

Also, ideally, I would like this Module to also Compact the database rather than just reminding me to do so manually.

The code so far is:

Public Sub Module_Compact()

        If FileLen(CurrentDb.Name) > 150000 Then

        Dim PrintMsgBox As Byte
        PrintMsgBox = MsgBox("File Size getting too big.  Please Compact.", vbOKOnly + vbExclamation, "WARNING - FILE SIZE TOO BIG")

        End If

End Sub

Many thanks to you all for any help you can give me.

Viv

[dsatino]

The following will work,but I think you need to look into why your DB is constantly growing too large. 2 GB is not all that large by any means, but the fact that you can compact it and regain any significant space probably means your using too many temporary tables and need a better strategy for what you're doing. It definitely sounds like you need to split your DB as well.


As for where to put it...I'd say at the end of any process that is causing your DB to grow substantially.

Public Sub Module_Compact()
    Dim x As Integer
    Dim tf As Boolean
    
    tf = False
    
    If FileLen(CurrentDb.Name) > (0.9 * 2147483648#) Then
        x = MsgBox("File Size getting too big. Database will now compact.", vbOKOnly, "WARNING - FILE SIZE TOO BIG")
        tf = True
    ElseIf FileLen(CurrentDb.Name) > (0.75 * 2147483648#) Then
        x = MsgBox("File Size getting too big. Would you like to compact it now?", vbYesNo, "WARNING - FILE SIZE TOO BIG")
        If x = 6 Then tf = True
    End If
    
    Application.SetOption "Auto Compact", tf
    If tf = True Then DoCmd.Quit
End Sub

[VivDenham]

Hi dsatino

Thank you so much for such a speedy reply. And what's more, it has worked. Just one more thing..... once the database has compacted, it closes (as instructed in your DoCmd.Quit). I would like the database to remain open - do I just leave out the DoCmd.Quit Line?

And yes, I do agree that I have too many temporary tables - these are caused by Make Table Queries overwriting the originals. However, if I don's use these MTQs, and just use a series of queries, I get an error message "Query Too Complex", hence using tables.

Many thanks for your interest.

Viv Denham

[dsatino]

You can leave that line out, yes. But then it won't compact until you close the DB. So it really depends on whether you want it to happen directly after the dialogue box or not.

If you're going to keep the MTQ strategy then I strongly suggest that you put the temporary tables in another DB. Constantly compacting your DB could cause corruption and loss of your DB in it's entirety.

[VivDenham]

Hi dsatino

Once again, thanks for such a speedy reply. For my purposes, I really want the Compact to happen so that the file size does not get too big and Access crash altogether, resulting in corrupt data. So I guess having Access close is the better of the 2 evils.

And yes, again, I agree with you that I need to split the database. This has been a steep learning curve for me - I am making a database for all the Machine Knitting designs I knit, and am completely self-taught (I am a 65-year old silver surfer!!!). So I will do as you suggest, and split the database. Many, many thanks for your help.

Regards
Viv Denham

[NeoPa]

Anyone getting into Access at that stage of their lives deserves a whopping thumbs up from all of us here. Be prepared for things to get tricky and complicated Viv, but full marks for your attitude :-)

[VivDenham]

Tee Hee!!! Thanks for that - made me laugh out loud. My husband says "Too Late! Things have been tricky for years with this database!" I originally had the brainwave in 2001 with the intention of automating my knitting calculations using Excell - but then my son suggested Access - and then the trouble began. I had dark hair then, now it's white!!!

Best regards to you all, and thanks for all your help.

Viv

[VivDenham]

Hi - Just as I thought I'd got it sussed.....

The solution provided by DSATINO works perfectly. However, I have noticed that every time it requires the database to be Compacted, it then unchecks the checkbox for Compact On Close in the Access Options.

This means that when the database is NOT too big to require a Compact, it does not get Compacted on closing the database.

I've got mixed feelings about this. Compact on Close is something I've always used, but lately I've been seeing people saying not to use it at all. What are your opinions? Is there a way to retain the tick in the Compact on Close checkbox and still use dsatino's code, or should I not bother with the bloat?

Many thanks for your ideas.

Viv

[dsatino]

The problem with compacting that it's actually a misnomer in that you're not actually compacting the DB, rather you are replacing it. What Access does is create a new DB, write all of the good data over to the new DB, delete the current DB, and then rename the new DB to that of the deleted DB.

If anything glitches during the write process, you could be left with a corrupt DB and no way to recover your data. Anybody that's worked with Access long enough has probably had this occur.

To be honest, I really think you need to re-evaluate the process that is causing the bloat.

If you want to .zip the DB and post it, I'll take a look at the process for you and point you in the right direction.

[VivDenham]

Hi dsatino

Thank you so much for your extremely kind offer to look at my database. I have zipped it, but can't for the life of me see how to insert it into my reply to you. I'll keep trying to find out and will send it to you asap.

Thanks again
Viv

[dsatino]

Clicked the 'Advanced' button and then from that screen click the 'Manage Attachments' button and upload it through the popup screen

[zmbd]

Welcome

<...> - please bear in mind that I am an absolute beginner!!! - <...>

Hello and Welcome to Bytes!

I must say it sounds like you’ve really started quite the ambitious project!

Before you get too much farther into your project may I respectfully offer a few links that might help you with your current and future projects:

With all of the MTQ being discussed, and the problematic query SQL‘s; I suspect that you may have a need to review how the database is “normalized” and maybe the over all logic in the RDMS design.

As you already have a RDMS set up, let’s start with “Normalization: ” For me, the following tutorial was a good refresher with the concept of "normalizat ion" and it has a really good explanation of the concept for those just starting out: Database Normalization and Table Structures.

IMHO: This is the number one problem in database design and is very much like playing Chess… very easy to learn the basic rules and a #### difficult game when played. Using a lot of MTQ is very symptomatic of either a normalization issue or some other issue… You really shouldn't be running into issues with 2GB. As DSantio points out 2GB isn't a large storage space; however, 2GB should hold something like 1 million pages of unformatted 10pitch/Dbls/1"margn typed text (say around 20 sets of the Encyclopedia Britannica at 25ish-vol/set including some of the pictures). For the average user, that's a lot of space. My Mom (65yrs) has a very old PC with 4GB harddrive (I know) and it has over a 1000 pictures stored on the drive (thankfully, backed up to DVD now - love vacation I.T. work)

As for the other issues, the solution I have is this tutorial covering the basics of relational database management system (RDMS) design. Now I understand that you’ve already dived in and gotten wet (YEA!!! Can‘t swim if you don‘t get in the pool!); however, I’ve found that all too often the textbooks really do a very poor presentation of the actual steps behind setting up a database from scratch. However, I have found a really good step by step tutorial for basic design here: A Tutorial for Access
Although this may sound a tad late for your current project, this tutorial may yet give you some insight to any issues you may run into with it in the near term and should help you with any future projects (may inspire you to start from scratch … no…. don’t blame you! Several DB I use made by others that have way too much data to mess with and… well… I don’t fix them)

Of particular note is tables page within the tutorial - notice the information that is given, the table name, the field name, the field type, key or index, etc... AND the format used to present that data. When you post back, this is the type of information we'll need to help you along in your project. You don't need a fancy grid table to do this... just a line by line layout.

Now for one more issue you will run into with any text from Microsoft… lookup fields in tables... I avoid lookup fields at the table level! And here is why: :Are Lookup Fields in Tables Evil? ... despite the fact that these are supported in MSAccess, they are not supported in other databases. More importantly, writing queries based on tables with look-up fields later on will be somewhat problematic. IMHO: The ONLY exception I've seen for this deals with a share-point integration.

There will be other people that argue that look-up fields are just fine at the table level... I side with the MVPs that do this stuff for a living… unless using share point, don’t use them at the table level… only use them in queries, forms, and reports.

Finally, so that you can really get the help you need, and the other experts, mod, and users can follow what you’re asking in the future, the following are really a must read:
How to ask good questions
Posting guidelines

My apologies for the long post… just a lot of ground to cover--- and I tend to ramble... a little...

Most Respectfully
-Z
What do you mean it's still 95 outside... it's 3am!>.... oh... time for a nap :)

[VivDenham]

Hi Zmbd - thanks for your reply - gosh, 3.00 a.m. and you are still awake enough to think properly!!!

I have read the article on Normalisation, and I'm pretty confident that my tables comply with this. In the past, I've tried to get over using MTQs by using SQL Server, but had to give up with that when it came to the calculations.

By calculations I mean that I have an item of knitting, say a Jumper. Before I start knitting, I need to know that I have enough yarn to finish it.

Therefore, I calculate the Knitting Instructions (number of stitches and number of rows). This is the first layer of queries - Knitting Instructions.

Then I calculate the Stitches for every part of the Jumper (i.e. Back, Front, Sleeve, Collar) and for every section of that part (i.e. Welt, Body, Armhole, Neck). This is the 2nd layer of queries - Stitches - using calculations from the queries Knitting Instructions.

Then I calculate the Projected Weight of yarn for each of these sections of knitting within each part of the Jumper, by using the Stitches calculated above and multiplying by the Weight per Stitch from the Tension Square details. This is the 3rd layer of queries.

Then I add all these Projected Weights together to give the total weight for the Jumper - the 4th layer of queries - and here I get the message "Query Too Complex" (and sometimes even at an earlier stage than this).

Hence my son told me about using Make Tables to give Access less work to do running all these cascading queries.

And this works perfectly well, and gives me the results I want. Except that every time I run the MTQs I get bloat and need to Compact. The actual file size, compacted, is 375,000kb. Without all the Make Tables, it would only be half this size, but without them, I wouldn't be able to calculate the Projected Weight of Yarn required.

This probably sounds all very trivial to you, someone who is used to dealing with companies trying to make a success of their businesses. But for someone from the Knitting World, it is vital to know you have enough yarn before you start on a project.

So, once again, thank you for all your (and everyone else's) efforts at Bytes. My son tells me that I am trying to get Access to do something it is not capable of, that I'm using a database to do something it's not meant to do. But I'm determined to get as far as I can with it.

Thanks again

Viv

[zmbd]

Viv

giggle
You've tossed us a Rubik's Cube!
giggle - I suspect that the people here love puzzles

Makes perfect sense as for what you're trying to do. My Wife crochets and I used to know how to do really (really, really (x100)) simple knitted stuff (ok, so they were usually just pot holders, tried a sock once... three needles... Chemist, not Seamstress)

I'm using my Mom and Dad's PC; thus, no MSOffice for the next week; however, I'd love to see your database and tinker with it once I'm home and DSatino seems to be fairly sharp with this too given that D has already offered to take a look when you get a chance you should upload the file. This really sounds like something MSA should be able to do... even within a single query... or better yet a form with a few dropdowns...

(giggle... bring on the puzzle!!!)

Did you figure out DSatino's instructions on how to upload your zipped DB?

-z
Ok... I really need to take a nap before the kids get up and the Wife wants me to help (3yr-Twins(S&D) and a 9yr DD that thinks she's 20... when did that start! :) )

[VivDenham]

Hi -z

I did try to upload the database, but got an error message that a Security Token was missing. I'm awaiting an update from Bytes Support team. Then I'll try again. If any of you would have any time to look at the database, it will be interesting to know what you feel.

I know I've built in lots of "Catch-Outs" (e.g. I've put a "Locked" field on every form, linked to a Module for every form. I had found that I was clicking on input fields and inadvertently changing the data unintentionally . Now I have every field locked until I click the "Locked" checkbox to unlock them. Long winded I know, but 65-year old idiot-proof!!!

Anyway, as soon as I hear from Support, I'll try to upload the DB again. Watch this space!!!

Viv