MS ACCESS 2003 - code to execute compact/repair in case database gets corrupted or should I do some thing else?

All this was just covered here:

https://www.experts-exchange.com/questions/22488067/Compact-an-Access-database-before-close.html

Check it out ...

mx

Especially read this:


http://www.trigeminal.com/usenet/usenet023.asp?1033

Has to do with using the Menu C&R vs DAO / ADO ... ie ... using a 'button', etc.  

mx

You say "they are running through my VB application", but then you also say "Also the FE is a MDE" ... so is the frontend a Visual Basic app, or is it built in Access and deployed as an .mde file?

To answer your questions:

1) That depends on the type of corruption. The builtin C&R will fix most minor corruption issues, but the goal should be to reduce as much corruption as absolutely possible.

2) If you're running an Access application, then you can use this;

DoCmd.RunCommand acCmdCompactDatabase

If you're running a VB app (using an Access database as the BE) then you'd need to use the DAO or the Jet Replication Objects to do this. Here's a link about that:

JRO:
http://support.microsoft.com/kb/230501

You can use the CompactDatabase method with DAO:
dao.CompactDatabase "Source", "Destination"

btw ... not trying to avoid the Q ... but, no sense rehashing, repasting , etc ... that thread is only a couple of days old.

Good stuff ...

mx

LSM .... see Kaplan's article re the DAO / ADO approach. And, that was just reprinted in Access Advisor and confirmed by Ken Getz ... fwiw ....

>>> http://www.trigeminal.com/usenet/usenet023.asp?1033

mx

RE: rehashing - yes, no need to do so, but I don't see where the link you posted would address the posters first question ( Does the built in compact/repair feature fix most corruptions). I've certainly not done a thorough job of that either, but IMO a link to a prior question isn't a complete answer to the posters question (especially when that link also points to another link where the accepted answer is, in my opinion, NOT a good way to handle this operation). It also doesn't really allow the poster the ability to post "follow up" questions to the Experts from the linked thread, thus if the answer doesn't exactly fit their needs, or if they're not clear on what's happening, then  they're kinda left "dead in the water".

Also, my experience has been that many (if not most) posters have already searched EE and have seen the links provided, and perhaps need a bit more guideance.

I was unclear about the application environment - if the user is running a VB frontend, then none of the solutions offered by Mitch (or the other link, for that matter) would really apply very well (except, perhaps, for the utility provided by Mitch). If they're running a full Access application, then the suggestions you provided are right on the mark.

Sorry to both of you... I was a bit misleading...  I should not have used VB... I meant to say that the client will be using my application programmed in MS ACCESS "VB" mode.... not true VB... I just woke up and will go over all these suggestions today.
Please bare with me for a couple of hours.

Thank-you in advance.

There is an application available from Microsoft called Jetcomp.exe that will fix more problems than Access's inbuilt compact and repair

http://support.microsoft.com/kb/295334

LSM:

"but IMO a link to a prior question isn't a complete answer to the posters question (especially when that link also points to another link"
Well, ALL of the top 'experts' here do that on a regular basis, so ... no need to fault me for doing it.

"'Also, my experience has been that many (if not most) posters have already searched EE"
Hardly the case at all :-(

However, I was interested in your take on the Kaplan article??

Re JetComp.exe ... Not to take anything away from cquinn, but sadly in my experience, I've never had any luck with jetcomp fixing anything corrupted, where as C&R does a pretty good job.  Also, that utility has not been kept up to date.

mx

ok So i've gone through all the links and links and read most of it...

1) Yes again (newbie) I meant VBA (Visual Basic for Application ?!?)
2) I believe I almost have the answer. I just need to clear it up a little bit more.

My application FE is MDE
1) Does an MDE need to be compacted/repaired?

(Grr... Enter key)
I thought a MDE wouldn't be able to get bigger or corrupted?!?

2) All I was hopeing to do.. Is put a command button on a form only a "admin" in my program can get to that would compact the BE.... (I have already made a check to ensure no one else is logged in.. at least using my application)
So can I make the FE-MDE file compact the BE-MDB file while only 1 user is on the FE?
?
Thank-you both!

"Does an MDE need to be compacted/repaired? "

YES.  It is just as important to C&R a FE (mde, mdb) as the BE.  C&R does important 'house cleaning', including, but not limited to ... checking for bad/corrupt indexes/pointers, fully removing deleted records that may be in temp tables, and a load of other good stuff. Also, it 'decompiles' saved queries ... such that ... the next time they are run, they are 're-optimized / compiled' ... **based on the quantity of data at that moment** - a very good thing! Overall,  it's good overall preventative maintenance !

mx

"So can I make the FE-MDE file compact the BE-MDB file while only 1 user is on the FE?


YES ... **as long as** ... at that moment ... there are no 'objects' (forms, reports, queries, etc) that would have ANY of the tables open in the BE ... then you are good to go.  For example ... if you open a typical FE  (mdb)   ..that is **linked** to a BE ... holding down the shift key ... and do not open any object that is connected to a table in the BE ... you will notice that ... there is no BE LDB file at the moment. Thus ... you could execute a C&R on the BE at that time, if you see what I mean?

mx

DarabaseMX,
so for the FE, since it is on all machines I can simply check the box to compact on close
AND THEN make my MDE of the FE
AND THEN that will automatically compact eveyone's FE when they close down?

Follow up question... by doing that, it won't compact the BE correct?

DatabaseMX,
Since my application removes all ways of getting to Access... including the shift... (yes you can still get around those) BUT I want them to compact the BE without knowing about access and everything else... so I can not get them to SHIFT into the application to ensure nothing is open.... It appears I can not do it this way.

However in one of your responses there was a link to a utli page which autobackec up the BE.... can I do an auto compact on the BE and somehow check if anyone is logged into it?

<Well, ALL of the top 'experts' here do that on a regular basis, so ... no need to fault me for doing it.>

I didn't fault you for doing it, I merely stated my opinion that the link didn't fully answer the poster's question (and I'm not sure that ALL the top Experts do this, but a good many certainly make ample use of EE's search engine, quaint as it may be). In my opinion, the accepted answer of the question you linked to used a method I don't agree with, and have had numerous issues with (it made use of the FileSystemObject, which is quite often blocked by antivirus or other security engines). Sorry if you took offense to this, but EE is a collaborative site and there are MANY times when another Expert disagrees with the tact I took for a question and offered alternative suggestions ... that's just part of the game.

<"'Also, my experience has been that many (if not most) posters have already searched EE"
Hardly the case at all :-(>

That's not been my take on the situation, but everyone sees things differently. In my experience, most posters have gone to pretty good lengths to try and figure out what they're doing wrong, and that usually includes either a Google search, or an EE search ... either way, the answers on EE will show up. Again, just my experience and (obviously) your's varies.

<However, I was interested in your take on the Kaplan article??>

My take on the article is this: Mitch Kaplan has forgotten more about Access than any of us here will ever know and I can guarantee that his insight if far more relevant than mine.  The article is VERY relevant, since we've determined that the poster is using a full Access application. Of course, as he points out, Access 2002 fixed many of the issues the with the builtin DAO methods, so the poster (who's using 2003) would be able to use the builtin methods to perform their Compact and Repair.

"so for the FE, since it is on all machines I can simply check the box to compact on close
AND THEN make my MDE of the FE AND THEN that will automatically compact eveyone's FE when they close down?"

Yes ... you *could* do that, however ... that might be a bit of overkill.  Ideally, an 'admin' person would do that to the FE's on a 'regular' basis. But, if that's not practical ... or you cannot really do that, then I would say yes rather than no.  Note that ... this will slow down the time for Access (your MDE) to close ... just so you know.

"Follow up question... by doing that, it won't compact the BE correct?"
Correct. It will NOT compact the BE.

"However in one of your responses there was a link to a utli page which autobackec up the BE.... can I do an auto compact on the BE and somehow check if anyone is logged into it?

Well ... yes I suppose.  You can determine if someone is logged in (Connected) to the BE ... other than the user who might execute the 'automated' BE C&R.  This can be done by examining the BE LDB file ... among other ways, if this is what you mean?

I will be offline for 3 hours or so ... it's now 18:00 PST

mx

ok DatabaseMX.. you've answered the compact question for the FE... I'm happy to check that box and save the MDE so everyone on close compact's their FE... (I don't mind a little extra shut down)

So just to clear up the BE.

If I check COMPACT on close for the  BE.MDB then would that compact the BE when the last FE user logs out?

follow up question... So if Monday, 2 people open up the FE which opens the BE and both are in at the same time, when the first person logs out of the FE, the BE would do nothing but as soon as the second person logs out of the FE, then the BE would compact?
And this would do the same thing the next time someone loads the FE and closes it (if they are last?)

Ok I really understand the relationship between FE and BE and why FE won't affect the BE check box to compact on close. Thank-you

The  utility would be great to us, however I don't want my users to have to go to the file and touch it that way. (I will not be there to open/close the BE and my uers are not database savy...)

so is there some way at a specified time (like that backup util you showed) to open and close the BE so it will compact?

(Almost there. :>)

Ok I found one.... Here is a program that will allow you to backkup (even use Zip), and compact/repair

If you use a scheduler program then it will be automated.

http://www.peterssoftware.com/cal.htm

Thank-you all

75 points to LSM for the questions answered
rest to DatabaseMX

Thank-you again.

Yes ... I'm GLAD you gave some pts to LSM ... he makes some of the most intelligent, useful and **detailed** posts in this Zone ... and I have logged and noted several (ie added to my EE KB) ... Including this thread.

Hey ... check out Peterssoftware Shriker-Stretcher ... true genius.  I bought the $35 version ... and implemented a 'sizable' Zoom box .... and get the right click Compact/Decompile Registry add-in .... beyond cool and a HUGE developer time saver!

Thank you ... it was fun.

Rock On Motor City ...

mx