Automating the compress function of a database

I desparately need to find a solution to the mysterious bloating of databases.  I reviewed the article in Access VB Advisor Feb 98, but I feel my program is too complicated to invoke that solution.  Does any one know of a white paper or way to automate the process of compressing the database, or a means to prevent the bloating?

I'm using many querydefs to display data combined with Temporay tables.  The database is doubling in size every 3-4 days.  My client is at a remote location many miles away It's simply imposible to drive 50 miles every 4 days to run the compression utility.
LVL 3
SE081398Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ozphilCommented:
I provide the compression utility in the users menu bar. The user simply clicks on it. Just drag the Access compression menu item to the users menu bar. Or invoke as a form button action or whatever.

Let the user do the compression. You can provide a file size alert on startup if you wish to alert the user to compress the database.




0
rstonecipherCommented:
i've seen this before and i think this worked:  since you can't compress a db without first closing it, you have to write a vba module of some kind to close the current database, compact it to a temporary .mdb file, delete the old one and rename the temporary with the new name.  you can automatically run the sub at the time of the db close or install it as a menu option.  (if you install as menu option, do above, but reopen after renaming the temp file).  it's a pain really but if you're client isn't familiar enough with access to use the menu function, this should work.  lemme know if you there's anything else.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JimMorganCommented:
rstonecipher:

Since you are fairly new to EE, you probably don't realize the protocol here.  We do not force an answer when there are one or more experts who have already provided comment(s).  We never answer a question unless we are ABSOLUTELY, POSITIVELY SURE that the answer is the best and only solution for the question.  When you lock a question, you discourage other experts from responding to the question.

The poster of the question has an option to make any comment, which he/she feels is the best solution, as an answer.

SE:  First, add a shortcut to the program which does a Repair and Compact on the desired database.  The user can use this anytime that they want.

Is the database a multiuser database on a network?  If so, I hope that you have split the database into two parts - the data and the program.  Since you have a lot of temporary tables, put the temporary tables in the program side and keep the program side on the users local drive.

During close of the application, remind the user that the database needs to be compacted on a regular basis and ask if they want to do that now.  If the response is Yes, create a small trigger file on the local directory.  In the shortcut that calls the application, use a batch file and add another line which checks for the existance of the trigger file.  If the file is not there, go to the end of the batch file and quit.  If there is a file, then run the shortcut that compacts the local program db for the application.  When finished, delete the trigger file.

Doing it this way will be of two benefits:  there resources and overhead are reduced and the app runs faster, the bloating doesn't affect anyone else other than the individual user, the data database does not have to closed to get rid of the bloat files, the data database will only have to be compacted if there are wholesale deletions from the tables.

When the data is linked, Jet doesn't touch the DB other than to get data and put it back.  All the overhead (bloating) takes place in the DB with the program code.

Jim
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

ozphilCommented:
Wow Jim i think you've put the fear of death into rstonecipher!

You're right - I was reluctant to contribute further to this question.

I've been away from EE for a long time but the protocol you describe sure is a big improvement on the bad old days - I wasn't aware that the questioner could select a comment as the answer. Probably rstonecipher wasn't either.

As far as automating the compression goes, there is an alternative way of determining when to compress. I know for instance that in my previous project the 'client' mdb with all it's temporary tables was about 6mb when compressed, and bloated to 20-30mb over a period of activity - hours or days.

If on database startup or close this filesize was measured by the program code and exceeded a value of 20mb then the code would display a message to the user and begin the compression process with the application compression method.

That's about as simple as I could make it. But we have'nt heard from SE yet as to which approach would be appropriate for his application.


0
BrianWrenCommented:
If you additionally kept the temporary tables in a separate DB locally, linking them to the app, then you could use the main app to compact the data-receptacle db wothout having to close anything.

You could use time to trigger the compaction, (on a calendar-basis, not a clock-basis), No. of times the app has been opened since last compact, or button click.

If your app contains only presentation objects, (forms, reports), and code, it shouldn't swell in size at all.

Brian
0
JimMorganCommented:
Brian:

I believe that even presentation objects can have app bloat.  I have one application with a db with reports only and it grows.  The reports and forms call the queries and keep the result set internally.  It is supposed to go just in memory but unless you have over 64MB of RAM, a lot of those results go inside the DB.  They are not flushed when the DB closes.

The separate DB for temp tables would work if it was the only one which grew.

Jim
0
DedushkaCommented:
Hi all.
Good news: Acc2000 has a new feature - you can set a parameter "Compact on Close" in the Database options :-)
0
dsegardCommented:
Dtomyn provided me an excellent solution using a tool that compacts the current opened db. And it works fine.

Btw Jim : you were new here too a few months ago. If you kill all new guys that does not respect ALL rules at first time, you'll be alone soon to answer Q. Are you always perfect ? Do you expect everyone to have 0 default ? Read my english...

DS
0
JimMorganCommented:
dsegard:  Whoa!  Back off.

You are right.  I was new here a few months ago and I got chewed out myself a few times from some of the best ones.  I didn't take it as a personal attack and I hope that rstonecipher didn't see it that way.  This was one of my milder butt-chewers also.

The problem is not the new guys, it is EE for failing to provide any etiquette rules for new members to follow.  And it seems as if each topic has its own set of rules.  In some topics, it is the first man who gives the answer gets the points.  I'm glad that we are more intelligent and sophisticated in Access that we don't have that kind of atmosphere or I'd been out of here a long time ago.

There has been a rash of members answering questions with the very answers provided by another expert's previous comments.  That is a percentage play that a certain number of members will feel too embarassed to reject the answer since technically it is correct although it wasn't first.

These are the same people who will find questions which have been sitting around for a while with no resolution, give a BS answer, and hope that the autograde will kick in before the member comes back.

I am hardly perfect.  Anyone who has been reading any of my responses would know that.  Also I have no expectations other than this is a very valuable site where people can go to obtain knowledge and share their experiences.

Have I done something in the past to apparently make you angry?  Please let me know what it was and I'll make amends.

Jim
0
dsegardCommented:

Absolutely not. I'm often happy to read your answers. I have no reason to be angry - I hope I'll never have.

I only think to new Access people.

I understand they should answer more carefully but we should tell them they are wrong.... carefully and... friendly.

You wrote "... obtain knowledge and share their experiences".

You resume all what I think about EE.

Friendly,

DS
0
ozphilCommented:
Hey dsegard (SE)

Could you provide some details on the compaction tool you mentioned above?

Thanks

0
dsegardCommented:

"CompactCurrent".

He probably get it at
http://www.mvps.org/access/index.htm

Which has also a lot of interesting things.

Don't have it now but can send it tommorrow from office.

DS
0
ozphilCommented:
Thanks a lot dsegard. Long time since I looked at that site.

It is a good site. I'll look for it myself there.
0
JimMorganCommented:
dsegard:

I agree with what you say about new members, however, we have had a rash of new members lately who have been ignoring our 'warm and friendly' etiquette suggestions so several of us, not just me, have taken to letting a new member who crosses the bounds know right away and somewhat forcefully what we expect.

If you have a more friendly, but effective method, I'm sure that we all would be interested in it.

Everybody's friendly!!!  :-)))

Jim
0
SE081398Author Commented:
Boys.....

Good Christ !!!!!!!!!!!!!!   what is this ... the jerry springer show??

I've read the FEW answers that dealt with the question at hand and need a day or two to research them so I better understand how I can solve my problem, and, how I'm going to assign points.  

This is the third time one of my questions had been answered by 2 or more people.  It places me in a difficult position

I'll be in touch with further information.

SE.
0
ozphilCommented:
Jerry Springer? Who the heck is that?

Did you ever see Fawlty Towers, where John Cleese complained that the hotel (EE) would be great without the customers (experts).

Just pick the answer you'll be applying. If you're going to apply parts of several answers, well that does make it difficult. How is that resolved Jim?

If you feel the nicest guy should get them, well there's no question..me!
0
SE081398Author Commented:
Jerry Springer...   the show where freaks get up on stage and fight and argue over the craziest things.  You don't know that show???

And ya I remember the faulty towers episode  that was funny.

And yes I have had to split points in the past, I wish I didn't have to.

I checked out that site mentioned above and I couldn't find any reference to compactcurrent().

0
dsegardCommented:

I do have this tool from Dtomyn with function to use in a module. I wrote he probably got it there.

If you are still interested, send a short mail to... david.segard@eurotunnel.com
.... and I'll reply with attachments.

Hope this helps,

DS
0
ozphilCommented:
SE

I think I have heard of Jerry Springer but have never got around to watching it. I suppose EE provides me with the same sort of entertainment you described. :-)).


If you use the search menu item in that site and enter the keyword 'compact', you'll get some items in the list (French/English).


0
dsegardCommented:
Yes but I can't find the one mentionned above... Ozphil : if you want a copy, no pb.

DS
0
JimMorganCommented:
This is no Jerry Springer Show.  No freaks (that I know of), no one fighting, and no one arguing over the craziest things.  Our things are very sane, thank you.

Now I'm getting a rep for not being nice.  How about to the person who has been on a dry spell getting points lately - me?  :-)

Jim
0
ozphilCommented:
ok dsegard. ozphil@bigpond.com
0
JimMorganCommented:
ozphil: Put you email in your profile.  When it is put in an open forum, all the little spam spiders will find it and you're on their spam list for life.
0
TrygveCommented:
The Jerry Springer Show has got to be one of the worst program ideas ever. It also displays the pathetic double set of morals which Americans live under (no offence). It is perfectly OK to show people punching each others faces, pulling hair, kicking etc. but the tinyest attempt at bad language or any "immoral" display of bodyparts will be censored...

I have found the show amusing from time to time though; "I am having a baby with my brother", "Why won't you have sex with me and my sister?", "Why did you sleep with my boyfriend, dad?". These are all headlines that makes great shows ;-)


As for Jims attempts at letting new experts into the secret ethics of the Access area: I fully agree with him and have found his postings quite acceptable. This one does not follow the normal "template", but I don't think it is too nasty. Let's hear it from rstonecipher.
0
JimMorganCommented:
"I am having a baby with my brother"?  I guess that I missed that show, and the others you mentioned.

Actually, other than surfing the channels, I don't think that I've ever tuned in the show on purpose and if I happen to see it, I watch maybe 2-3 mintues and then move on.

The last nanny for my Grandchildren had to watch Springer every day, along with my GrandKids.  My daughter got rid of her as fast as she could.  My youngest Grandson was starting to mime the people on the show.

Apparently Springer is a bigger hit in the UK than in the US.  I don't know about the rest of Europe.
0
dsegardCommented:

Not known in France except when TVs look around for fun : we laugh when we see such ridiculous shows driven by a stupid "nightmare" team.

That does not means we want to keep the secret if same things happens here but put that in front of millions of people is disgusting and gives satisfaction to deviant minds only.

Hope I didn't hurt...

DS

0
SE081398Author Commented:
You know what would be nice, is a breif stat on each EE user so we all know who we're talking to.  For instance: the country we live in, our gender, and maybe our profession.  

Here in Canada we get most of our entertainment from the states.  I don't know why UK would watch Jerry Springer when they have Red Dwarf?  now that's good.

......
Still trying to work out my problem with compacting.



0
TrygveCommented:
Did you check out the article which dsegard provided you a link to?
0
SE081398Author Commented:
Before I new about searching for compactcurrent.  I've been tied up with other problems and hope to get to it tomorrow.
you know how it is.
0
SE081398Author Commented:
Tryqve: yes I just went there and down loaded the compactor.mda got an error" unable to open, unknown database name"

besides addins can not be accessed unless you have the Access menu available, which my program does not.  

The user has to choose compact from the custom menu bar and compact the running database which they are in.  The users do not no how to work in Access, everything has to be automated.

If you know of an addin that works, and can be run from a custom menu bar, let me know.

0
ozphilCommented:
Here's an alternative way to do it all from Access.

It actually works.

Put all of the following code in your application db (say mydatabase.mdb). When you run CompactDBstart() from a menu or form it does it all:

1. creates a new empty database
2. transfers a module and auto macro to it
3. shells out to the new database
4. closes itself
5. meanwhile the new database compacts the database which just launched it (weird)
6. the new database shells out to the database to the databse that launced it, and closes ITself.

Modify it to suit your needs.
I just did it out of interest, and you may prefer some other way, that's ok.

*** basCompactDB ***
Option Compare Database
Option Explicit
 Function gsExtractDirectoryPath(sFilepath As String) As String
    Dim pos2 As Integer, pos As Integer
   
    pos2 = 1
    Do While True
    pos = InStr(pos2, sFilepath, "\")
    If pos = 0 Then
        pos = pos2
        Exit Do
    Else
        pos2 = pos + 1
    End If
    Loop
    gsExtractDirectoryPath = Left(sFilepath, pos - 1)

 End Function

function CompactDBstart()
    Dim dbsNew As Database
    Dim sDir As String, sTempDB As String
    Dim vntDummy As Variant
   
   
    ' Create file in whatever the current dir happens to be ( for purposes of this test)
    sDir = gsExtractDirectoryPath(CurrentDb.Name)
    ' Make sure there isn't already a file with the name of
    ' the new database.
    sTempDB = sDir & "db1.mdb"
    If Dir(sTempDB) <> "" Then Kill sTempDB

   
    Set dbsNew = CreateDatabase(sTempDB, dbLangGeneral)
    vntDummy = Shell("msaccess.exe " & sTempDB)
   
    ' Export module to this new db
    DoCmd.TransferDatabase acExport, "Microsoft Access", sTempDB, acModule, "basCompactDB", "basCompactDB"
    DoCmd.TransferDatabase acExport, "Microsoft Access", sTempDB, acMacro, "RemoteCompactautoexec", "autoexec"
    Quit
   
   
End function

Function compactDB()
    Dim sDir As String, sAppDB As String, sAppDB_temp As String
    Dim vntDummy As Variant
   
    'pause 3   ' give db time to close
    ' Get directory path
    sDir = gsExtractDirectoryPath(CurrentDb.Name)
    sAppDB = sDir & "myDatabase.mdb"
    sAppDB_temp = sDir & "myDatabase1.mdb"
    If Dir(sAppDB_temp) <> "" Then Kill sAppDB_temp
    Name sAppDB As sAppDB_temp
    CompactDatabase sAppDB_temp, sAppDB
    vntDummy = Shell("msaccess.exe " & sAppDB)
    Quit
End Function

*** MACRO ***
RemoteCompactautoexec
contains:
RunCode = =compactDB()




0
JimMorganCommented:
ozphil:

That sounds nice but of course it may not work with an MDE or secured database as I suspect this one is, without some specific changes to the security to allow exporting or importing anything from the DB.

I still stand by my initial suggestion that it be handled outside of Access as part of the shortcut which calls Access in the first place.  Since this also appears to be a mulit-user database, I don't believe that other users can be in a database when it is being compacted.

SE, you really should have a system administrator reponsible for general housekeeping at your customer site.  At the end of the day when everyone is out of the program, the SA can run the repair and compact as part of the nightly routines, like backup.

I really feel that you should address the problem along these lines.  It is the safest and most reliable way to do this.  If you tell the customer how critical this is, then once they start the routine of doing it, it will be done without fail.

Jim
0
ozphilCommented:
Access applications come in many flavours ( some not so yummy :) ). It may suit SE. It's quite possible it's not an MDE and it's not a network database (eg a 'local client' mdb which contains a lot of and all of the temporary tables.)

To automate it, soemwhere in start up add code something like below.

const MAX_FILE_SIZE =3000000
if FileLen(gsExtractDirectoryPath(CurrentDb.Name) > MAX_FILE_SIZE then
     msgbox ("Compacting File. Press OK to continue.")      
      dummyRet = CompactDBstart()
endif


   
0
SE081398Author Commented:
Actually, ozphil your right, it's not a secured database or an mde.  I've decided to build a visual basic app that performs the task of compacting the mdb.  The VB.exe is shelled out from the mdb and starts the process.  I've taken some ideas from many of the suggestions above. By the way a database canot be compacted until it's closed, which leads me to this problem.
the database is linked to a back end with the tables.  This is the close method I've used: access.closecurrentdatabase
guess what?  the closecurrentdatabase can not close a linked database.  does anyone know the function or method in VB that closes a linked database?  or do the links have to be removed and relinked after the database is opened again?  Once this is all done I'l be happy to email the final VB program for all to review.  Considering the mass interest in this topic.  There are so many good ideas found above, I think it's only fair everyone see's how their hard work help me.

0
AlexVirochovskyCommented:
I think, you must simple close all
objects(linked too). next is code:

Sub sCloseAllObjects()
Dim i As Integer, ctr As Object, j As Integer
Dim db As Object
Dim astObj(0 To 5) As String
    astObj(0) = "Tables"
    astObj(1) = "Queries"
    astObj(2) = "Forms"
    astObj(3) = "Reports"
    astObj(4) = "Scripts"
    astObj(5) = "Modules"
    On Error Resume Next
    With mobjAccess
        Set db = .currentdb
        For i = 0 To 5
            Set ctr = db.Containers(astObj(i))
            For j = 0 To ctr.Documents.Count - 1
                .DoCmd.Close i, ctr.Documents(j).Name, mcSave
            Next j
        Next i
    End With
End Sub
0
JimMorganCommented:
Are the linked tables done throught the Access DB or through VB.  If part of the Access DB, then closing it will close the linked tables.  There is no need to break the links and relink.  The compact process only does its work on the objects physically in the Access DB.  If you want to compact the linked tables as well, then you will have to compact the DB which contains the linked tables, assuming that they are in an Access DB.

Jim
0
SE081398Author Commented:
Jim:  the linked tables are done through access.  It appears that closing a database through VB, which is linked to another database causes the VB program to hang.  the closecurrentdatabase method doesn't seem to work properly.  I didn't foresee this kind of problem.

Example:  

DB1 is linked to DB2
the VB app closes db1 via access.closecurrentdatabase
because db2 is still open db1 won't close causeing it to hang.
0
JimMorganCommented:
I still think that the best way to do this is to close the VB app which will close all Access DBs and after the close, run another program which will do the compression.

Using VB, you can really automate the process much easier than in Access.  Call the VB program from within another VB program which is called from a shortcut.  When you need to compress the Access DBs, set a flag to indicate to the opener program that the compression needs to be done and then return you to the VB app.

When the VB app is closed and no flag set, then the opener program can close.

The user would be none the wiser that the app has closed.  Just show them a message asking them to be patient while some housekeeping is being done.  This could even be done during a normal break period, lunch, or after hours, or even after a long period of inactivity.

Jim
0
SE081398Author Commented:
My sincere appologies to everyone involved with this question, I should have attending to this question sooner.  I've been very busy and distracted with work, I forgot all about it.  Anyway, I was able to construct a solution using VB and the Access app I needed to close.  Primarily I shell out to a VB.exe which opens.  by clicking a button on the VB form it then closes the Access app and starts compacting it.  I have ziped up the VB app And a sample db1.mdb to show how I did it.

I'd like to forward this zip file to everyone who would like to see my solution.   simply email me and request it and I'll forward it along to you (IAL@cablelan.net) .  I actually solved this problem in December, but due to the hollidays and my work load I forgot to respond till now.

I hope this will clarify this question.
0
JimMorganCommented:
Did this question autograde?
0
mksilkCommented:
ozphil,

I have used your code to perform the compact procedure and it works however it does produce an error message of:
"Could not use C:\fullpath\db1.mdb; file already in use"
When I click on OK, it has performed the Compact anyway so everything works, it just shows this annoying message. Any ideas?
0
JimMorganCommented:
This is essentially a warning that you are about to Compact over an existing file.  It doesn't come across as such but that is what is happening.  If I remember correctly, in order to maintain a version control of our programs, we would name the Compact file to a version name.  Then we would verify that the version file was working properly.  If so, we'd copy that file over the existing file and move the version file to a backup directory.

Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.