Solved

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

Posted on 2007-04-04
23
370 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:detroitdr
  • 10
  • 8
  • 4
  • +1
23 Comments
 
LVL 75
ID: 18855153
0
 
LVL 75
ID: 18855158
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
 
LVL 84
ID: 18855159
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
 
LVL 75
ID: 18855163
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
 
LVL 75
ID: 18855171
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
 
LVL 84
ID: 18856969
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
 

Author Comment

by:detroitdr
ID: 18857075
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
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 75 total points
ID: 18857351
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
 
LVL 15

Expert Comment

by:cquinn
ID: 18857869
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
 
LVL 75
ID: 18858576
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
 

Author Comment

by:detroitdr
ID: 18861689
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:detroitdr
ID: 18861694
(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
 
LVL 75
ID: 18861740
"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
 
LVL 75
ID: 18861763
"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
 

Author Comment

by:detroitdr
ID: 18861816
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
 

Author Comment

by:detroitdr
ID: 18861828
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
 
LVL 84
ID: 18861868
<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
 
LVL 75
ID: 18861979
"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
 

Author Comment

by:detroitdr
ID: 18865919
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
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 425 total points
ID: 18866298
"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
 

Author Comment

by:detroitdr
ID: 18866358
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
 

Author Comment

by:detroitdr
ID: 18866517
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
 
LVL 75
ID: 18866599
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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

747 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

14 Experts available now in Live!

Get 1:1 Help Now