Solved

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

Posted on 2011-03-01
9
464 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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
 
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

Technology Partners: 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!

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
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…

749 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