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

Hello,

I'm pretty new to MS Access 2003. I've got a full registration program going and it totally locks out all of Access menu's (soon will do shortcuts). From what I've been reading about Access's databases is that they can "easily" get corrupted. (I have sharing on and record locking on both the BE and FE).

Since I have not experienced a corrupted MS Access database yet, I can not tell if the built in Access "compact and repair" feature would fix most corrupted tables in the database...

(The users will not have access to the tables or Access themselves.. they are running through my VB application so I'm looking for an answer they they would be able to do themselves.)

***Also the FE is a MDE***

So my questions are:
1) Does the built in compact/repair feature fix most corruptions? (i understand what the compact side does...)
2) If yes then what code can I use to have the user click a command button to compact/repair
    If not.. what is my best solution to cover my butt.

Thank-you
detroitdrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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"

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
detroitdrAuthor Commented:
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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
There is no "VB" mode of Access ... you apparently mean you're using VBA to code your application.

If this is the case, you'd want to use a third party utility (or build something yourself). The utility that mx showed you will do the trick, but you'll have to deploy and install it.

An easier solution might be to include a shortcut in your installation package that would fire the /compact method of the msaccess.exe program. Typically you'd add this to the Programs menu item when your program is installed. The syntax is something like this:

"Full path to msaccess.exe" /compact "full path to your database"

In any case, note that ALL users must be out of the backend database before you can compact or repair.

Here's a good KB article on corruption and such:
http://support.microsoft.com/kb/283849
 


0
cquinnCommented:
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
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
0
detroitdrAuthor Commented:
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?
0
detroitdrAuthor Commented:
(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!
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"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
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"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
0
detroitdrAuthor Commented:
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?

0
detroitdrAuthor Commented:
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?

0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<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.


0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"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
0
detroitdrAuthor Commented:
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?)

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"If I check COMPACT on close for the  BE.MDB then would that compact the BE when the last FE user logs out?"

No. Assuming Compact **is checked in the BE**,  You would have to manually open the BE and close it. That is all that the Compact On Close check box refers to / does, ie Manually open/close >>> then it will compact.

FYI --- HERE (http://www.mvps.org/access/modules/mdl0039.htm) ... is a VERY handy little 'right click' (in Windows Explorer) goodie that I have been using for years:

Get It ... be sure to set the paths for you Office/Access install.  I use this multiple times daily

"follow up question... So if Monday, 2 people open up the FE which opens the BE"
No ... same reason.  And ... The FE is not really 'opening' the BE per se ... it is just simply linked to the tables in the BE ... any of which may or may not be Open (ie, being accessed).

Man ... this is a workout .. lol ...

And hey ... be sure to say Hi to Joe Zimmer if you ever do meet him!

mx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
detroitdrAuthor Commented:
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. :>)
0
detroitdrAuthor Commented:
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.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.