Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

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

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
SASnewbie
Asked:
SASnewbie
  • 2
3 Solutions
 
Rey Obrero (Capricorn1)Commented:
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
 
SASnewbieAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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

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!

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