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,706 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
  • 8
  • 8
  • 4
  • +2
24 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) 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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

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

9 Experts available now in Live!

Get 1:1 Help Now