Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

brianjohnson (TechnicalUser)
29 Aug 03 17:44
I've been trying to crack this for some time. In Access 97, it's possible to comnpact the current database from the Tools, Database Utilities and Compact menus. However when trying to do the same in code, you get a request for the database to be compacterd from and to as in :-

CommandBars!tools.Controls![Database Utilities].Controls![Compact Database].Execute

Can anyone say why this is so ? I'd assumed that this code does the equivalent of clicking the menu ... am I on the wroing track ?

Regards

Brian

Helpful Member!  jsteph (TechnicalUser)
29 Aug 03 18:05
It would be theoretically impossible--since the code that is running is part of the .mdb being compacted, so there is an open form or module from which the code runs that has to still be there when the code completes.

The code (the call the the application menu item) can't 'jump ship' and be out on it's own in the ether while it's home--the mdb--is being compacted.

When the menu item is run, it is the Access application--not the .mdb which--is running menu item so the .mdb can be closed.

Are you familiar with the dbengine.compactdatabase xx,xx command?  
--jsteph
brianjohnson (TechnicalUser)
30 Aug 03 13:52
jsteph

Thanks for replying. I see your point. But given that it is possible to compact the database, when a form is open, by using the Tools Menu, is it the case that the menu system takes the "current" database, opened form and record pointer, closes the form and the .mdb, compacts it, reopens the database and the form at the last record ? I can understand that (if that's the case) but if the menu options can do this, why can't it be achieved in code without having to specify the db to be compacted and the name of the newly compacted db ?

...or have I missed the point again ?

Regards

Brian
jsteph (TechnicalUser)
30 Aug 03 15:28
If a form is open when you choose Compact, it'll close the form automatically (and any other open objects), prompting for unsaved work.  When the db reopens, it just runs any autoexec or startup scripts as it normally would, but it doesn't remember the state you were in (ie, which forms, etc. were open and what record you were on) and recreate that--it just starts like you just opened it from scratch.

But the whole point of the menu method (as opposed to code) is the context of where the compaction code was called.    Basically, the code can't 'kill itself'. So if Cust.mdb code calls Access' compaction code, the Cust.mdb code can't complete if it has to kill itself.  Here's what basically happens:

Let's say you have Cust.mdb.  What Compact--The Access Application code *not* code in Cust.mdb--is actually doing is Closing Cust.mdb, creating a new blank db, naming it (usually db1.mdb, or db2.mdb, etc.) and then exporting all objects from Cust.mdb to db1.mdb, which automatically rebuilds table indexes and consolidates dead space (space taken up by deleted records--where the recs are gone but the space is still used).  Then if this happens with no errors, it (Access code--not Cust.mdb code) deletes Cust.mdb and Renames db1.mdb to Cust.mdb.  

The Delete part is mainly why Cust.mdb code can't do that--the code would be deleting itself, then who's going to rename the .mdb?  So in your code, you need to supply the New name, and then you manually have to close Cust.mdb, then you can delete it and rename whatever new name you gave it--BUT--this is because here we're talking about doing it from the *context* of code in Cust.mdb, where the Menu example the context is Access, the application, not cust.mdb.

I hope I explained that correctly, I know it's a bit confusing but I think the gist is looking at the context of where the code is actually running.

One option is that you can open the .mdb with a shortcut and give it a /Compact switch.

If you must compact from the user interface of the runnin mdb, a kludge would be to put this (Pseudocode) in a New db say, MyCompactor.mdb and have it run on StartUp of Mycompactor.mdb:

Wait 5 'give origdb time to close
dbengine.compactdatabase myOrigdb,Tempdb
kill myOrigdb
rename tempdb origdb
ShellExecute origdb
quit

Then you can, in your db do:
ShellExecute Mycompactor.mdb
Quit

This would run on some 'compact' button.  ShellExecute is needed as an API declaration, but it's pretty straighforward.
--jsetph
Helpful Member!(4)  WildHare (MIS)
2 Sep 03 15:39
Uhh.. toss this little puppy on a command button:

Private Sub Command1_Click()
   CommandBars("Menu Bar"). _
   Controls("Tools"). _
   Controls("Database utilities"). _
   Controls("Compact and repair database..."). _
accDoDefaultAction
End Sub

The kicker here is the last line. Note the acCDoDefaultAction.

Most other built-in guys use acSomethingOrOther, with only ONE "C" - I don't know if this was a deliberate thing or an acCident.. but this method works for A2K and above.

If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:
www.forumco.com/jmhare
More Access stuff at
www.jmhare.com/wildhare.htm

Eupher (MIS)
3 Sep 03 1:43
Another star for you, WildHare.  I managed to accomplish the same result using SendKeys, but didn't like the menus popping up as the code executed.  Your solution is much better.  Thanks!

Ken S.
mikeyb540 (Programmer)
3 Sep 03 18:30
I like your solution So I gave you a Star
shelby55 (TechnicalUser)
31 Mar 04 0:24
Hi WildHare

I know this thread is kind of old but I was excited to see your solution.  However, I can't get it to work.  I'm using Access 2000.  Any particular reference that I need to check for?  My code is as follows:

Private Sub cmdCompile_Click()
CommandBars ("MenuBar"), _
Controls("Tools"), _
Controls("Database utilities"), _
Controls("Compact and repair database..."), _
accDoDefaultAction
End Sub


Thanks WH!

Shelby
PHV (MIS)
31 Mar 04 0:32
Replace the commas with dots

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

fabby1 (TechnicalUser)
31 Mar 04 4:23
Hi Brian

I use this function to compact my database when it is over a certain size.  I check it every time I exit and compact if necessary.

CODE

Public Function AutoCompactCurrentProject()
    Dim fs, f, S, filespec
    Dim strProjectPath As String, strProjectName As String
    
    strProjectPath = Application.CurrentProject.Path
    strProjectName = Application.CurrentProject.Name
    filespec = strProjectPath & "\" & strProjectName
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(filespec)
    S = CLng(f.Size / 1000000)  'convert size of app from bytes to Mb’s
    If S > 20 Then              'edit the 20 (Mb’s) to the max size you want to allow your app to grow.
        Application.SetOption ("Auto Compact"), 1  'compact app
    Else
        Application.SetOption ("Auto Compact"), 0   'no don’t compact app
    End If
End Function

Hope it's of use

Phil
shelby55 (TechnicalUser)
31 Mar 04 6:23
Hi WH

Thanks for the quick reply but I'm getting a run-time error with the code:
Private Sub cmdCompile_Click()
CommandBars ("MenuBar"). _
Controls("Tools"). _
Controls("Database utilities"). _
Controls("Compact and repair database..."). _
accDoDefaultAction
End Sub

The error is "run-time error 5 invalid procedure call or argument".  Any thoughts on what I'm doing wrong?  Thank you!!

Shelby
BobStubbs (Programmer)
31 Mar 04 10:01
I have just posed a FAQ on this subject - there may be some help for you in there.  See FAQ705-4998 - Compact and Repair database at the start of each day.

Bob Stubbs

Helpful Member!  susanhawk (Programmer)
1 Apr 04 13:05
I have a VB6 application that I use to compact any version of access using the function below.  I use input boxes for the user to input the version, and the password (in any).
The only part I can't get working is the Security part for databases using Workgroup security.  It does work for all the hundreds of other Access applications on our hospital server.
Based on the version entered I set the JetEngine type as shown in the first code snippet.

    If RS!DBType = "97" Then
        lnVersion = 4
    ElseIf RS!DBType = "2000" Or RS!DBType = "2002" Or RS!DBType = "2003" Then
        lnVersion = 5
    End If
....various other code to pass parameters to function...

Public Function Compact(sSource As String, sDestination As String, Optional sSecurity As String, Optional sUser As String, Optional sPassword As String, Optional lVersion As Long) As Boolean
    Dim sCompactPart1 As String
    Dim sCompactPart2 As String
    Dim oJet As JRO.JetEngine
       
    'build string for source database
    sCompactPart1 = "Provider=Microsoft.Jet.OLEDB.4.0" & ";Data Source=" & sSource & ";User Id=" & sUser & ";;Jet OLEDB:Database Password='" & sPassword & "'"
    
    'THIS IS WHERE I WOULD LOVE SOME HELP!
    If sSecurity <> "" Then
        'TRIED ALL THREE BELOW AND NONE WORK
        sCompactPart1 = sCompactPart1 & ";Jet.OLEDB:System database=" & sSecurity & ";"
        'sCompactPart1 = sCompactPart1 & ";Jet.OLEDB:System database=" & sSecurity
        'sCompactPart1 = sCompactPart1 & ";Jet OLEDB:System database=" & sSecurity & " "
    End If
    
    'build string for destination database
    sCompactPart2 = "Provider=Microsoft.Jet.OLEDB.4.0" & ";Data Source=" & sDestination
    If sPassword <> "None" Then
        sCompactPart2 = sCompactPart2 & ";User Id=" & sUser & ";;Jet OLEDB:Database Password='" & sPassword & "'"
    End If
    
    If lVersion <> 0 Then
        sCompactPart2 = sCompactPart2 & ";Jet OLEDB:Engine Type=" & lVersion
    End If
  
    'compact and give original name to compacted database
    Set oJet = New JRO.JetEngine
    oJet.CompactDatabase sCompactPart1, sCompactPart2
    Set oJet = Nothing
    Compact = True
End Function

Hope this is helpful and I hope someone can return code to compact databases protected by workgroup security.
PHV (MIS)
2 Apr 04 21:16
shelby55, pay attention to your menu. I haven't english version, so I can just guess:
Controls("Compact a database..."). _
Another guess is to use Execute method, as in my version (2003) accDoDefaultAction doesn't exists.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

rss01 (TechnicalUser)
2 Apr 04 22:03
I use Access 2000 and on the Tools menu, Options, General Tab check the box Compact on Close. Whenever you close the database it comapcts.
Helpful Member!(2)  jksmi (TechnicalUser)
3 Apr 04 8:24
Hi, have a solution that is quite flexible based on getoption and setoption of the auto compact on close that rss01 uses...
on a switchboard or admin form i put a checkbox 'chkCNR' in the form_open event I add the following...

CODE

Private Sub Form_Open(Cancel As Integer)
chkCNR = Application.GetOption("Auto Compact")
end sub
this ties the checkbox to the compact on close you see on the options dialog, then I add the following to the afterupdate event for the check box

CODE

Private Sub chkCNR_AfterUpdate()
Application.SetOption "Auto Compact", chkCNR
End Sub
to set compact on close option on or off...
finally I call the following from an autoexec macro or startup form to the auto compact off next time its opened...

CODE

Public sub CheckAutoCompact()
If Application.GetOption("Auto Compact") Then Application.SetOption "Auto Compact", False
End sub
you dont have to use a form, you could test a value on a table or test the database size but the principles the same...
tcstom (Programmer)
6 Apr 04 10:50
Does anyone know if any of these solutions work with Access 97? Or are they all Access 2000...?

Tom

TheQuestioner (Programmer)
2 Jun 04 8:02
What if you want to compact a linked database? Don't the methods outlined above only compact the current mdb (i.e the front-end if you've split the database)?

This is the problem I'm having with my Access 2000 database?
TheQuestioner (Programmer)
3 Jun 04 13:07
Managed to solve my problem in another thread (Thread705-853257).

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close