Solved

How is a Compact and Repair performed on a database that is not opened?

Posted on 2014-10-23
4
229 Views
Last Modified: 2014-10-23
Hello,

I have databases with tables that are utilized by an Alteryx application as input and output. The end user will not be opening the databases.

The issue is the growth of the databases due to internal Access operations which can be close to 1GB in some instances.

Is there a way to set Compact and Repair for these databases without opening them?

Thanks
0
Comment
Question by:SASnewbie
  • 2
4 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 450 total points
ID: 40399471
you can use vba codes run from another access app to compact and repair the target db
, just make sure that nobody is connected to the targetdb

Sub CnR()

Dim objAcc As Access.Application
Set objAcc = Access.Application

objAcc.DBEngine.CompactDatabase "C:\folderName\targetDb1.mdb", "C:\folderName\targetDb2.mdb"
Kill "C:\folderName\targetDb1.mdb"
Name "C:\folderName\targetDb2.mdb" As "C:\folderName\targetDb1.mdb"

end sub
0
 

Author Comment

by:SASnewbie
ID: 40399481
Hi Rey,
Thank you for your quick response.

Is there a way to lock the database through VBA as well while performing the C&R?
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 450 total points
ID: 40399544
you can check for the presence of the targetDb.ldb or targetDb.laccdb in the folder where your target db is, which means that somebody is connected to your db..

if dir(<path to your targetdb> & "\*.laccdb")="" then
    'run the C&R
   else
    msgbox "DB in use!"
end if
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 50 total points
ID: 40399556
<<Is there a way to lock the database through VBA as well while performing the C&R? >>

 A C&R requires exclusive access and won't let anyone connect to it while it is running.

Jim.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

743 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

13 Experts available now in Live!

Get 1:1 Help Now