Post your favorite VB or VBA-based Access app automated compact-repair

Hello experts

{Token salutations, 'long time no see', where have you been, congrats on your multiple certs, you're still a big fat ex-PE meanie, where's the $10 you owe me, yada yada yada}

I have multiple Access '97 apps (yes, I know, '97) that needs to have a single batch job run about 30 times for 30 different clients, and for about 4-5 of these it gets real close to the 1Gb limitation and close to 5 hours long.  Right now the runs are manual, but there is a table that contains each client that can be easily automated to support a loop to execute the batches sequentially.  

The batcheswill also range anywhere from 5 minutes to 5 hours, so scheduling isn't really an option.
Upgrading to Access '2007 is an option, but that by itself isn't an answer I'm looking for.
Compact on Close is an option, but that by itself isn't an answer I'm looking for.
Token 'rewrite your code' comments aren't the answer I'm looking for, but I may award points based on style.

Questions:
    (1)  Does the newer versions of Access (2007, 2010) have the VBA-based ability to repair-compact?
    (2)  Does anyone have any VBx-VBA code that can repair-compact another Access db, specifically that does this within a loop, that can automatically detect when a batch job of unknown duration is complete before executing the next loop?

Thanks in advance.
Jim

LVL 66
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Nick67Connect With a Mentor Commented:
OK,

My DB's have a hidden dummy form that keeps the connection to the backend alive.
The first snippet is code from them that put in a marker to the filesystem to show that the db is open.

The second snippet is a VBScript that opens a db, sends the compact and repair call to it, and waits for that to finish before closing it.
Now, my DB has some Msgboxes that open right away and I have to dismiss those before the db will close.
YMMV
Option Compare Database
Option Explicit

Private Sub Form_Close()
'Call TestKill

'declare an object to be a filesystem object
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists("m:\dev\IamOpen.txt") Then
    fs.DeleteFile "m:\dev\IamOpen.txt"
End If
Set fs = Nothing

End Sub

Private Sub Form_Open(Cancel As Integer)

'declare an object to be a filesystem object
Dim fs As Object
Dim BatFile As TextStream
Set fs = CreateObject("Scripting.FileSystemObject")
Set fs = CreateObject("Scripting.FileSystemObject")
Set BatFile = fs.CreateTextFile("m:\dev\IamOpen.txt", True)
BatFile.WriteLine ("I am Open")
BatFile.Close

Set fs = Nothing
Set BatFile = Nothing
End Sub

Open in new window

On error resume next

'VBScript to serially compact any number of db's
'delineated in MyDBs(X)
'Enjoy and keep this notice intact
'Nick67

Dim s 'As String
Dim BuiltPath 'As String
Dim x 'As Integer, number of db's to compact
Dim wait 'As Double
Dim success 'As Boolean
Dim CompactingSuccess ' as boolean
dim MyDBs(0)
dim response
dim CurrentPath
dim CompactPath
            
Dim fs 'As Object
Set fs = CreateObject("Scripting.FileSystemObject")

MyDBs(0) = "M:\decompile\TI_Prog.mdb"
s="m:\dev\Iamopen.txt"
CompactPath = "m:\decompile\Compacting.mdb"

dim PathToMDE
PathToMDE = s

Set acApp=CreateObject("Access.Application.11")
acApp.usercontrol=true
acApp.DoCmd.RunCommand (10) 'accmdappmaximize
acApp.AutomationSecurity=1  ' low

x = 0
'Do Until success = True
    CurrentPath =  myDBs(x)
    'msgbox currentpath
    acApp.OpenCurrentDatabase CurrentPath
    CompactingSuccess = acApp.CompactRepair(CurrentPath, CompactPath)
    success = fs.FileExists(s) Or x = 1
    If success = False Then
        wait = Timer
        While Timer < wait + 60
            DoEvents  'do nothing
        Wend
        success = fs.FileExists(s)
        x = x + 1

    else
    'for testing
    'msgbox "Compaction Complete"
    AcApp.Quit
    End If

'Loop

Set acApp=Nothing
Set fs = nothing

'msgbox "Bailed"

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
here is my 2¢


dbengine.compactdatabase db1, db2
0
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
<<dbengine.compactdatabase db1, db2 >>

 Gotta watch that.  Depending on version, a dbEngine Compact is not the same as a compact through the UI.

<<(1)  Does the newer versions of Access (2007, 2010) have the VBA-based ability to repair-compact?>>

  The restriction still exists that you cannot compact the currently open DB, but yes it still a method of the dbEngine object.

<<    (2)  Does anyone have any VBx-VBA code that can repair-compact another Access db, specifically that does this within a loop, that can automatically detect when a batch job of unknown duration is complete before executing the next loop?>>

  Nothing handy, but I'm sure a search on EE would turn up plenty.  Also you might be interested in this as a starting point:

http://www.trigeminal.com/lang/1033/utility.asp?ItemID=2#2

JimD.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
BTW, on this:

<< that can automatically detect when a batch job of unknown duration is complete before executing the next loop?>>

  I have code to wait for a shelled process to finish, so it you want to stick with the batch files or just call Access directly with the /Compact switch, that certainly would be doable.

JimD.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Shakier still, you could get out your hammer:

   SendKeys "%(FMC)", [Wait]

(Set the WaitState argument accordingly)
0
 
mbizupCommented:
JH,

Nothing to add technically -- just nice to see you around.
0
 
Nick67Connect With a Mentor Commented:
That'd be fun.
My VBScript deployment routine checks a file named ver.txt for values and then deploys front-ends
It could be adapted so that it would look for a file that indicated a particular db was open.
If the db's create a file with filesystemobject on opening, and erase on closing, then an absence of a file would mean compacting was ongoing.
Throw in a loop and doevents and you're there.

Only the compacting Access is running, and a lightweight VBScript, so its less resources
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
We decided to go a different route with this, and build a scheduler .mdb, with one open form and a Timer event, that will have table-driven batches.
We'll work in the repair-compact after each batch run to deal with the 1Gb size limitation.

There will be other benefits to the scheduler, mainly consolidating the menus (aka Switchboards) of all of them, centralizing Batches to make updates easier, being able to generate a list of all Access apps we have in production off of it (somewhere around 600), and tying that list to my logging .mde so we can completely report on report metrics such as when it ran, who ran it, batch or individual mode, did it blow up, what's the top 10 reports ran so we can optimize them, which reports were never run so we can consider killing them, and other stuff.  

We looked at FMS total visual agent, and determined that it essentially ran DOS command lines, which we could do ourselves either in Access or in ActiveBatch.

Points awarded for generating ideas that led me to this solution.

Thanks all.
Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.