Compact and Repair File Menu Option on th fly

I create my menu bar on the fly using the msoffice2000 object.  Is there a nice easy way I can insert the compact and repair button into my menu bar?

If possible I would like to instert the menu option that you get in the standard menu bar under database utilities.  I have code to compact the database but I'd rather keep it simple.
LVL 6
billy21Asked:
Who is Participating?
 
mgrattanConnect With a Mentor Commented:
I think perove's example might be slightly off.  Perhaps the reference to "tools" instead of "&Tools".  You can try the following code; it creates the toolbar as well, so you might need to adjust it so the toolbar is either temporary or gets deleted before getting added again.  Note that some of the code may wrap in the EE comment window; make sure you adjust it accordingly.

Public Function Createtb()
Dim tb As CommandBar, tbCtl As CommandBarControl
Dim myCustomBar As CommandBar, myControl As CommandBarControl

Set tbCtl = Application.CommandBars("Menu Bar").Controls("&Tools").CommandBar.Controls("&Database Utilities").CommandBar.Controls("&Compact
and Repair Database...")

Set myCustomBar = CommandBars.Add(Name:="tbCompact", Position:=msoBarTop)
Set myControl = tbCtl
With myControl
   .Copy Bar:=myCustomBar, Before:=1
End With
myCustomBar.Visible = True

End Function
0
 
nico5038Commented:
Why no "autocompact" on closure.
A2000 has that function on the Tools/Options form tab General.

Nic;o)
0
 
billy21Author Commented:
Because compacting takes about 90 seconds.  Too long to wait every time I close the database.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
nico5038Commented:
When in the menubar editmode you can drag and drop an option from the bars to your custombar.
Did you try that ?

(It's however then gone from the "official" menu!)

Nic;o)
0
 
billy21Author Commented:
Nico,

I need to do it on the fly.  The menu bar doesn't exist before the code is run to create it.
0
 
billy21Author Commented:
U C when the form is openned it deletes any existing menu bars and creates a new menu bar.  The menu bar is set up so that new menu items can be created based on the files in a folder.  For instance, if I make a new screen cam help file.  All I need to do is drop it in the folder and next time I open my database it picks it up as a new menu item.  Of course the same goes if I remove a screen cam.  It no longer shows up in my menu.

I already have code that compacts my back end file when the database is openned but I also need to compact the front end file and I would like this to be a menu item.
0
 
hotbudareCommented:
[Assuming A2K]
You can create your own menu bar thru VBA code.
Just add a reference to "Microsoft Office 9.0 Object Library", create a nuew MenuBar object, create (or copy from existing menu bars) as many controls as you want for your menu bar. Seek help on the OnAction property of cmenu bar controls and create your own function for compacting & repairing your database. Since you don't want to use compact & repair when closing your application, I'd recommend you NOT to use any solution involving the SendKeys command. Choose solutions like the one found at http://www.trigeminal.com/lang/1033/utility.asp?ItemID=8#8

Let us know if you need step by step instructions ...

HTH/EQTA
T.S.U. Mario Osorio
Punto Fijo, Falcon, Venezuela
0
 
nico5038Commented:
I'm running into problems when trying to activate the compact e.g. by macro.
Guess A2000 still isn't accepting a compact on a running .mdb.
If the compact on closure isn't in question, then perhaps an outside job can be used. (Enabling e.g. a compact by the taskmanager every night at 00:00 hrs).
Just check:
http://www.mvps.org/access/general/gen0013.htm
for some extra info.

Nic;o)
0
 
billy21Author Commented:
hotbudare,

Perhaps you didn't read all of the previous comments.  I already have a custom made menu bar created from vba code using the Microsoft Office 9.0 Library.

You mention in your statement that I can copy menu items from existing menu bars.  How may I go about that?
0
 
peroveCommented:
As Nico said you cannot do a compac&reapir from vb or a a macro.
Insetad copy the menu point from the tools menu to your menu.

here is an example.
In this exapmple i frist create a new cutom menubar
then I copy menu object from toools menu to my menu



   ' Create a new floating toolbar and make it visible.
   Set CBar = CommandBars.Add(Name:="Sample Toolbar9", Position:= _
      msoBarFloating)
      CBar.Visible = True
'copy the compact to my menu
CommandBars("tools").Controls.Item(7).Controls.Item(2).Copy CBar, Before:=1


perove
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Another point on this thread is that the compact/repair available through the menu is not the same as doing a compact/repair from code.

One is going through the UI, the other through the VBA interface.  I don't remember if the difference applies to A2000 or not, but you might want to stick with calling a function that uses the compactdatabase method.

Here is a link to a MSKB article on how to setup a compact for a scheduled time.  It would also be simple enough to modify this MDB to accept the name of a MDB at startup, compact it, and then return to the MDB that called it, which would be passed as a parameter as well.

http://support.microsoft.com/support/kb/articles/Q209/9/79.ASP

HTH,
Jim.
0
 
hotbudareCommented:
billy21,
>Perhaps you didn't read all of the previous comments ...
==> Missed that ...

>You mention in your statement that I can copy menu items from existing menu bars.  How may I go about
that?
==> Seek in the help file for the Copy method of the following objects in the Microsoft Office 9.0 Object Library. It's perhaps one og the most interesting (and forgotten) methods for these ojects:
CommandBarButton
CommandBarComboBox
CommandBarControl
CommandBarPopup

HTH/EQTA
T.S.U. Mario Osorio
Punto Fijo, Falcon, Venezuela

0
 
mgrattanCommented:
If you're compacting the current front-end MDB, you'll need to close it first.  An excellent (free) add-in that allows you to do this is the TSI SOON utility (http://www.trigeminal.com/lang/1033/utility.asp?ItemID=8#8).  Here's an example of how the code works:

Public Function CompactMe()
Dim tsd As Object
Set tsd = CreateObject("TsiSoon90.Connect80")
With tsd
    .FileToOpen = CurrentDb.Name
    .Exclusive = False
    .CompactOld = True
    .CloseAll Application
End With
End Function

The command CompactOld will compact the database you currently have open.  The FileToOpen command can be used to either re-open the current database or to open a completely different database.

This code can easily be added to a function which can then be added as the "OnAction" property of a new command bar menu option:

With CBarCtl
      .Caption = "Compact"
      .Style = msoButtonCaption
      .TooltipText = "Compact Database"
      .OnAction = CompactMe()
End With

This utility requires the VB6 runtime files which can be downloaded from http://support.microsoft.com/download/support/mslfiles/Vb6sys.exe

Hope that helps.
0
 
hotbudareCommented:
Hi mgrattan, I guess u didn't read my frist post ...
0
 
mgrattanCommented:
hotbudare,

I think perhaps you didn't read mine completely.  I addressed both the issue of closing/compacting/reopening the database as well as placing the command on a toolbar dynamically.  Your post simply pointed the question-asker to the methods of using VBA to create menus, which he already knew.....
0
 
billy21Author Commented:
mgrattan's idea is the best suggestion so far but I would prefer not to include the vb6 runtime files in my installation package if I don't have to.

JDettman,

The problem with compacting at a designated time is that is assumes the client will leave the machine on.  It also assumes the client can absorb the huge amount of RAM that Access 2000 occupies and leave the db running.  This is going out to over 100 stores, there is too much room for things to go wrong.

Hotbudare,
Can you give me some specific instructions on how to copy the compact and repair menu item from the standard menu bar to my new menu bar?  If you can and it works the points are yours.
0
 
mgrattanCommented:
billy21,

I decided to try hacking out some code that would create a new toolbar and add a button to it that compacts the database using the built-in toolbar's functionality; there doesn't seem to be any other way to compact your database front-end without closing it first.  Here's what I came up with:

Public Function Createtb()
Dim tb As CommandBar, tbCtl As CommandBarControl
Dim myCustomBar As CommandBar, myControl As CommandBarControl

Set tbCtl = Application.CommandBars("Menu Bar").Controls("&Tools").CommandBar.Controls("&Database Utilities").CommandBar.Controls("&Compact and Repair Database...")

Set myCustomBar = CommandBars.Add(Name:="tbCompact", Position:=msoBarTop)
Set myControl = tbCtl
With myControl
    .Copy Bar:=myCustomBar, Before:=1
End With
myCustomBar.Visible = True

End Function

It seems to work just fine...
0
 
mgrattanCommented:
hotbudare,

Just a follow-up to your comment; I re-read your post again and noticed that you did provide a link to the TSI Soon utility.  I regret not having noticed it before.  Also, I realize the last comment I made uses your idea of copying an existing toolbar.  I was intrigued by the idea since I had not tried it before; so I experimented with it and figured out how it works.  It was quite tricky; you have to be very explicit when setting the variable for the CommandBarControl and the Help files do not give very good examples for this particular functionality.  

Anyway, I hope everyone who's monitoring this Q benefits from it.  
0
 
peroveCommented:
Billy:

<<The problem with compacting at a designated time is that is assumes the client will leave the machine
on.  It also assumes the client can absorb the huge amount of RAM that Access 2000 occupies and leave
the db running.  This is going out to over 100 stores, there is too much room for things to go wrong.>>

Well, I'm prp. far out here but...
...don't you need to have the DB exclusive in order to compact it?? Would there not be a problem if *everyone* of the 100 user could compact at *any* time??


<<Can you give me some specific instructions on how to copy the compact and repair menu item from the
standard menu bar to my new menu bar?  If you can and it works the points are yours. >>

I have alraady done so I my comment
07/30/2001 06:06AM PST

perove
0
 
billy21Author Commented:
Perove,

I said I am distributing to 100 stores, I didn't say i was connecting the 100 stores to my back end.  I wouldn't dare use MSAccess to do something like that.  Access couldn't even handle a 500 employee database I wrote for a Company in Sydney this year.  I had to rewrite the whole thing using VB and SQL server.  Access seems pretty piss poor all round really.  I can't even sell an application in the UK if its written in Access.

Anyway, I totally missed your comment.  I will try it, if it works, the points are yours.

billy
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Billy,

  Access works as advertised.  What gives it a bad rep is "developers" who don't understand the product and try to make it do things it was never intended to do.  For example, running over a WAN without using replication.

  In fact I once answered a question where a company was in the middle of a commercial product rollout and the "developer" was in a panic because they just discovered that Access locked on pages and not records (this was A97).

  If the product is used correctly, it works fine.

Jim.

 
0
 
mgrattanCommented:
perove,

I didn't see your comment either....strange.  If I had seen it I wouldn't have spent so much time writing the code that I posted!!!!  
0
 
billy21Author Commented:
JDet,

I've used access to do some pretty hard core things.  Including Artificial Intelligence Programming.  I agree it can do much much more than people give it credit for.  But I'm tired of my product constantly being devalued by the perception.

This seems to stem from the rampant snobery in the UK but there are companies who simply aren't interested once they hear the word access.  I don't have the same probs from US or Australian sales.

On the other hand.  If you have a db with 100,000 records and you try to query that table over a LAN it has to bring all those records to the local machine to be queried.  That is damn slow.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Billy,

 Agree fully and again, it's the image of the product.  Part of it is Microsoft's own fault as they market it as an end-user tool rather then a developer tool.

 This makes people conclud that it's easy, then they get in over their heads, and then they call someone like us<g> (guess I really shouldn't complain, should I?).

<<On the other hand.  If you have a db with 100,000 records and you try to query that table over a LAN
it has to bring all those records to the local machine to be queried.  That is damn slow. >>

  Actually, that may or may not be true.  If the criteria is against an index, then only part of the index comes over.  You can use the ShowPlan function to see how a query is executing in Access.

  Also, starting with A2000, you can now use MSDE/SQL server, which moves you from a file sharing setup as with JET, to a true client/server setup.

Take care,
Jim.
0
 
billy21Author Commented:
Perove,

I tried your solution.  It returns the error "subscript out of range"
0
 
billy21Author Commented:
mgrattan,

Set myCustomBar = CommandBars.Add(Name:="tbCompact", Position:=msoBarTop)
Set myControl = tbCtl
With myControl
   .Copy Bar:=myCustomBar, Before:=1     <<<<<< At this point I get the error "Object variable or with block variable not set"
End With
myCustomBar.Visible = True
0
 
mgrattanCommented:
By the way, if it works I think credit should still go to perove since he mentioned it first.  
0
 
mgrattanCommented:
That error occurs if the toolbar name already exists.  Try the following revision, which deletes it if it already exists:

Public Function Createtb()
Dim tb As CommandBar, tbCtl As CommandBarControl
Dim myCustomBar As CommandBar, myControl As CommandBarControl

For Each tb In Application.CommandBars
    If tb.Name = "tbCompact" Then
        tb.Delete
        Exit For
    End If
Next tb

Set tbCtl = Application.CommandBars("Menu Bar").Controls("&Tools").CommandBar.Controls("&Database Utilities").CommandBar.Controls("&Compact and Repair Database...")

Set myCustomBar = CommandBars.Add(Name:="tbCompact", Position:=msoBarTop)
Set myControl = tbCtl
With myControl
    .Copy Bar:=myCustomBar, Before:=1
End With
myCustomBar.Visible = True

End Function
0
 
billy21Author Commented:
It works!!!

Thanks MGrattan
0
 
billy21Author Commented:
>By the way, if it works I think credit should still go to perove since he mentioned it first.  

Well his didn't work and yours did.  He wasn't here to support his Answer and you were.  You get the points.
0
 
mgrattanCommented:
Cool, glad you got it working!  

0
All Courses

From novice to tech pro — start learning today.