Solved

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

Posted on 2011-03-01
9
459 Views
Last Modified: 2013-11-29
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

0
Comment
Question by:Jim Horn
9 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 35011611
here is my 2¢


dbengine.compactdatabase db1, db2
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 175 total points
ID: 35011719
<<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
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 175 total points
ID: 35011815
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35021738
Shakier still, you could get out your hammer:

   SendKeys "%(FMC)", [Wait]

(Set the WaitState argument accordingly)
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 61

Expert Comment

by:mbizup
ID: 35033990
JH,

Nothing to add technically -- just nice to see you around.
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 325 total points
ID: 35115004
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
 
LVL 26

Accepted Solution

by:
Nick67 earned 325 total points
ID: 35132772
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
 
LVL 65

Author Comment

by:Jim Horn
ID: 35453306
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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

707 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

16 Experts available now in Live!

Get 1:1 Help Now