Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2405
  • 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
ruavol2
Asked:
ruavol2
  • 8
  • 8
  • 4
  • +2
20 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
For sure :-)
0
 
ruavol2Author 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 MVP, Access and Data Platform)Commented:

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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
"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 MVP, Access and Data Platform)Commented:
"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 MVP, Access and Data Platform)Commented:
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
 
ruavol2Author 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
 
ruavol2Author 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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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