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
1,931 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 200 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 200 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 25 total points
ID: 36720575
Make a backup first, in case (rare) the compact causes a problem.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 200 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 100

Assisted Solution

by:mlmcc
mlmcc earned 200 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 200 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 100

Assisted Solution

by:mlmcc
mlmcc earned 200 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 200 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 75 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 100

Assisted Solution

by:mlmcc
mlmcc earned 200 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 75 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 100

Assisted Solution

by:mlmcc
mlmcc earned 200 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 200 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 75 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 100

Assisted Solution

by:mlmcc
mlmcc earned 200 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 200 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 100

Assisted Solution

by:mlmcc
mlmcc earned 200 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 200 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 100

Assisted Solution

by:mlmcc
mlmcc earned 200 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 100

Assisted Solution

by:mlmcc
mlmcc earned 200 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In this post we will learn different types of Android Layout and some basics of an Android App.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

738 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