• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2693
  • Last Modified:

MS Access Compact and Repair function: Is there a way to automate that process through a task scheduler and do you need to have a license of Access

I have a client that has a tool that creates an MDB file nightly. Over time this file grows large due to the update tables that populate. Most recently it grew to 2+ gigs as it went unchecked. With Compact and repair the database shrunk down to 300 mb. So we realized that the customer just does not follow up with procedures and thought we would attempt a batch file process we could schedule with task scheduler or maybe force them to buy MS Access 2007. They do not have it of course.

Can this kind of procedure be created in a batch file without Access like maybe VB code or something or would this process require Access.......or will both be needed a batch file and MS Access to complete this task.
0
Fletcher Burdine
Asked:
Fletcher Burdine
  • 8
  • 8
  • 4
  • +2
20 Solutions
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Yes ... because you can use the Command line option to do a Compact & Repair .... see this KB

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

You can execute that from a batch file ..


mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Example:

"C:\Program Files\Microsoft Office\Office12\msaccess.exe" "\SomeFolder\SomeDatabase.mdb" /compact  

mx
0
 
pdebaetsCommented:
Make a backup first, in case (rare) the compact causes a problem.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
For sure :-)
0
 
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
Thank you gentlemen. I am going to get my trusty sidekick......um.....mentor...mlmcc. He is the real talent here. I did this as we work much together and I want to make sure I asked the question correctly. I think I did but he can verify. Thank you.
0
 
mlmccCommented:
The methods you provide require MS Access to be on the machine.  The users we have do not own MS Access.  We are wondering if there is an Access runtime that I can use to build an application tht does a repair and compact so the users won't need to purchase a license of Access.

mlmcc
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:

I'm confused re "The users we have do not own MS Access."

Then how are you doing this:

"With Compact and repair the database shrunk down to 300 mb."

?

mx
0
 
mlmccCommented:
We uploaded the MDB to our server compacted it and returned it.

The MDB is a nightly dump of the QB file and is used for reporting purposes.  It is only updated at night through the dump process.  They only access it through the reports we built for them.  They have no need for Access.

They are probably willing to purchase an MS Access license but if there is a way they don't have to then they won't be able to open the MDB file by accident and mess with it.

mlmcc
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I'm not thinking of a way to do C&R w/o Access, although it can certainly be done with the Access Runtime install, because for example, I have a couple of clients that sell runtime apps - with a C&R functionality ...

mx
0
 
GRayLCommented:
If you check Tools, Options, General, and check [Compact on Close], now when you open the mdb in an Access or Runtime environment, and subsequently close it, it will automatically perform a Compact & Repair on shutdown.
0
 
mlmccCommented:
Since Access isn't on the machine and the users don't open it through Access I don't think that is an option.

The database is updated through an application from  a QB database and is then used for reporting.  The users don't sue the database except through Crystal Reports.

mlmcc
0
 
GRayLCommented:
mimcc:  Somewhere, the table must be opened in an Access environment - I would think.  That being the case, if the mdb Options were modified as I suggested above, on closing the mdb, it would automatically Compact & Repair, even though none of the asker's users use Access.
0
 
mlmccCommented:
It is opened for update by a program called AccessBooksRealtime.  I don't know how that opens it.  

The Crystal connection is through the Crystal Acess connector.   I don't think it is opened as Access to where the database would repair/compact on exit.

mlmcc
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"omewhere, the table must be opened in an Access environment - I would think."
It's probably using the OpenDatabase Method ....

mx
0
 
GRayLCommented:
It just seems strange that one would use the Access db structure to 'hold' data, yet never open Access.  Something new every day around here.  
0
 
mlmccCommented:
I just ran a report against one of my Access databases.  It does create an LDB file.  DOes that mean it is opening Access so it will repair & compact it?

If Access isn't on the machine will it still repair and compact i?

mlmcc
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"DOes that mean it is opening Access so it will repair & compact it?"
No.  For example, using the OpenDatabase Method from VBA code (which I do frequently) will result in an LDB file. Closing that 'connection' will deleted the LDB file.  But, this is not the same as manually opening the MDB, then closing ... and if Compact On Close is set ... compacting the db.

You could ... write some vba code to 1) copy the db to a system with Access... 2) execute the C&R ... 3) copy the C&R'd db back to the home location.  In fact, this is exactly what happens in my nightly backup and C&R operation of approx 25 BE's dbs on our server.

mx
0
 
GRayLCommented:
So where are we at?  ruavol2:  Do you have your answer?
0
 
mlmccCommented:
Can I write a VB program using the Office runtimes to do the repair and compact?  I assume Access is not required on the machine.

mlmcc
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I'm not sure how that would work.  However, what I suggested in the 2nd paragraph @ http:#a36906680 using the File System Object will work, and is probably the most straight forward.

mx
0
 
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
mlmcc:
Not sure what to post here for information that would be useful to future users. What do you think would be appropriate in this case....please advise.

I will reward points on effort and leave it for you to tell me whether it should be sent to the knowledge base.......your thoughts appreciated.
0
 
mlmccCommented:
Here is the code I wrote to do this.

Option Explicit

Private Sub Main()
'
'JRO.JetEngine
'
Dim oJRO As Object
Dim sDatabasePath As String

sDatabasePath = "C:\QuickBooks\ABRT\MCCMED QBES ABRT Data File.mdb"

On Error GoTo ErrFailed

'
'  Check if a tmp database exists
'  If so then Delete the existing temp database
'
   If Len(Dir$(sDatabasePath & ".tmp")) Then
      VBA.Kill sDatabasePath & ".tmp"
   End If

'
'  Create the Jet object
'
   Set oJRO = CreateObject("JRO.JetEngine")

'
'Compact the database
'
   oJRO.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDatabasePath, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDatabasePath & ".tmp;Jet OLEDB:Engine Type=5"

'
'Delete the existing database
'
   VBA.Kill sDatabasePath

'
'Rename the compacted database
'
   Name sDatabasePath & ".tmp" As sDatabasePath


'
'  Close Jet
'
   Set oJRO = Nothing
Exit Sub

'
'   Handle Errors
'

ErrFailed:
   Set oJRO = Nothing

End Sub

Open in new window


mlmcc
0
 
mlmccCommented:
The code only requires MDAC be installed on the computer with the Jet Engine included.

mlmcc
0
 
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
I could not get mlmcc to reward points for this so I gave everyone a little of everything. We both worked on this and he was the lead person. We knew it would offer some value and my apologies for the delay
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 8
  • 8
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now