Solved

Compact and Repair File Menu Option on th fly

Posted on 2001-07-29
31
255 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:billy21
  • 11
  • 9
  • 3
  • +3
31 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 6332471
Why no "autocompact" on closure.
A2000 has that function on the Tools/Options form tab General.

Nic;o)
0
 
LVL 6

Author Comment

by:billy21
ID: 6332487
Because compacting takes about 90 seconds.  Too long to wait every time I close the database.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6332508
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
 
LVL 6

Author Comment

by:billy21
ID: 6332525
Nico,

I need to do it on the fly.  The menu bar doesn't exist before the code is run to create it.
0
 
LVL 6

Author Comment

by:billy21
ID: 6332695
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
 
LVL 2

Expert Comment

by:hotbudare
ID: 6332886
[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
 
LVL 54

Expert Comment

by:nico5038
ID: 6333823
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
 
LVL 6

Author Comment

by:billy21
ID: 6334106
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
 
LVL 9

Expert Comment

by:perove
ID: 6334260
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
 
LVL 57
ID: 6334432
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
 
LVL 2

Expert Comment

by:hotbudare
ID: 6335218
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
 
LVL 14

Expert Comment

by:mgrattan
ID: 6335230
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
 
LVL 2

Expert Comment

by:hotbudare
ID: 6335263
Hi mgrattan, I guess u didn't read my frist post ...
0
 
LVL 14

Expert Comment

by:mgrattan
ID: 6336141
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
 
LVL 6

Author Comment

by:billy21
ID: 6336353
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 14

Expert Comment

by:mgrattan
ID: 6336471
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
 
LVL 14

Expert Comment

by:mgrattan
ID: 6336625
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
 
LVL 9

Expert Comment

by:perove
ID: 6339633
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
 
LVL 6

Author Comment

by:billy21
ID: 6340173
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
 
LVL 57
ID: 6340837
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
 
LVL 14

Expert Comment

by:mgrattan
ID: 6341331
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
 
LVL 6

Author Comment

by:billy21
ID: 6342752
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
 
LVL 57
ID: 6342854
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
 
LVL 6

Author Comment

by:billy21
ID: 6343222
Perove,

I tried your solution.  It returns the error "subscript out of range"
0
 
LVL 14

Accepted Solution

by:
mgrattan earned 300 total points
ID: 6343245
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
 
LVL 6

Author Comment

by:billy21
ID: 6343246
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
 
LVL 14

Expert Comment

by:mgrattan
ID: 6343247
By the way, if it works I think credit should still go to perove since he mentioned it first.  
0
 
LVL 14

Expert Comment

by:mgrattan
ID: 6343257
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
 
LVL 6

Author Comment

by:billy21
ID: 6343269
It works!!!

Thanks MGrattan
0
 
LVL 6

Author Comment

by:billy21
ID: 6343280
>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
 
LVL 14

Expert Comment

by:mgrattan
ID: 6343292
Cool, glad you got it working!  

0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now