[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2011-09-28
24
Medium Priority
?
2,299 Views
Last Modified: 2012-05-12
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
Comment
Question by:ruavol2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
  • 4
  • +2
24 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 800 total points
ID: 36720503
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
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 800 total points
ID: 36720507
Example:

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

mx
0
 
LVL 12

Assisted Solution

by:pdebaets
pdebaets earned 100 total points
ID: 36720575
Make a backup first, in case (rare) the compact causes a problem.
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 800 total points
ID: 36720584
For sure :-)
0
 

Author Comment

by:ruavol2
ID: 36790698
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
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 800 total points
ID: 36812685
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
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 800 total points
ID: 36812706

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
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 800 total points
ID: 36812851
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
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 800 total points
ID: 36812870
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
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 300 total points
ID: 36900130
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
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 800 total points
ID: 36900707
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
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 300 total points
ID: 36905781
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
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 800 total points
ID: 36905891
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
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 800 total points
ID: 36905940
"omewhere, the table must be opened in an Access environment - I would think."
It's probably using the OpenDatabase Method ....

mx
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 300 total points
ID: 36906058
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
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 800 total points
ID: 36906651
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
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 800 total points
ID: 36906680
"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
 
LVL 44

Expert Comment

by:GRayL
ID: 36911270
So where are we at?  ruavol2:  Do you have your answer?
0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 800 total points
ID: 36911843
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
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 800 total points
ID: 36911952
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
 

Author Comment

by:ruavol2
ID: 37047979
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
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 800 total points
ID: 37048170
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
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 800 total points
ID: 37048235
The code only requires MDAC be installed on the computer with the Jet Engine included.

mlmcc
0
 

Author Closing Comment

by:ruavol2
ID: 37160060
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Six Sigma Control Plans
Suggested Courses

656 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