Solved

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

Posted on 2011-03-01
9
460 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

895 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

12 Experts available now in Live!

Get 1:1 Help Now