Sonic wrote:
Everyone is running 2000 in our company, so the cross versions do not
apply in this case. We have checked for recordsets in all of the codes
and have emptied and closed them as well. I do have the original code
and will put in the autocompact as you suggested. I'll also have to
send out a message to instruct everyone to close out of the DB when
leaving for the day. Occasionally, users will keep this open
overnight. Thanks for the suggestions.
Sonic, you're missing the major point. You should not have multiple
instances of the same mde open at any one time. Make individual copies
for each user and have them operate it from their PC.
Here's what I do in such cases to prompt users to do their own
compacting when the mde/mdb gets a bit large.
I use a custom menu and create a menu or tool bar (depends on what you
want). When in edit menu mode I hold the control key down (make sure
you do this or you will regret it -see hereafter as to why) and click on
the Compact & Repair menu item (found in Tools->Database
Utilities->Compact & Repair) and drag it to my tool bar.
If you don't hold the control key down when doing this, your standard
menu "loses" the Compact & Repair menu item!
I then change the caption to "Optimization of <App Name> is STRONGLY
Recommended!", or something similar, and put an image there - my
favourite is the little bell from the Change button Image menu item when
right clicking a menu item in customize menu mode.
Next, I run a routine like the following on start up - usually on a
splash form's on open event, though I suppose you could put it in a
module and run it from an autoexec macro if you wanted:
Dim strPath as string 'location of front end
Dim lngSize as Long 'size of front end
'Check for overall file size
'If larger than recommended size, make compact menu item visible
'otherwise, make it invisible
strPath = Access.CurrentDb.NAME
lngSize = FileLen(strPath)
If lngSize > 5000000 Then
CommandBars("mnuMain").Controls.Item(6).Visible = True
Else
CommandBars("mnuMain").Controls.Item(6).Visible = False
end if
In the above example, the commandbars lines assume there are 5 other
menu items on my custom menu bar "mnuMain" before the compact menu item.
Adjust as per your own application.
With respect to the size the FE must be before the user is prompted,
what I usually do is compact the finished front end and then set the
lngsize "trigger" above to twice that of the compacted size.
The above is for a front end. Like your SQL back end, I usually write
my apps against non Jet (in my case, Oracle) back ends. Similar code
can be constructed for maintenance of Jet back ends, though of course
the menu on action function will be a procedure using the
CompactDatabase command....
--
Tim
http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
