Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-03-01
9
Medium Priority
?
470 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35011611
here is my 2¢


dbengine.compactdatabase db1, db2
0
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 700 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 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 700 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

704 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