Loop through All Access Databases in a Directory/Partition, and Backup, Compact, Repair, ... 2

[kjv1611]

Is there any safe way to loop through all Access databases and end-goal - compact/repair everything? Here's what I would LIKE to do if I can know there's a safe way to do it:
[ol I]
[li]Loop through all Access Database files - I'd just assume to use the FileSystemObject for that, so that's simple enough.[/li]
[li]For each database, I'd like to run these steps in order:[/li]
[ol A]
[li]Backup the database[/li]
[li]Run compact/repair on the database (would actually prefer to go ahead and do this way:[/li]
[ol 1]
[li]Decompile[/li]
[li]Compact/Repair[/li]
[li]Compile (usually done via VB Editor window)[/li]
[li]Compact/Repair[/li]
[/ol]
[li]If know for sure that the compact/repaired copy is stable, then delete the backup[/li]
[/ol]
[li]Move to the next database in line, do the same thing.[/li]
[/ol]

There is one other small caveat that may post tricky. That is we still have some people in one group in our department who are mainly or some using Office 2007, whereas everyone else is using 2010. Well, I've run into a weird delimna in the past, which the only clear way around was to create their database(s) in .mdb format:
[ol 1]
[li]I would create the database to be compatible with 2007 AND 2010, build with 2010 on my machine[/li]
[li]I then had them do some work in the database, using Access 2007.[/li]
[li]After some changes made, I compacted and repaired the database, but made no changes regarding compatibility.[/li]
[li]Next time one of them tried to open with 2007, they got the message stating, "This was built with another version of Access, and is incompatible with your version of Access."[/li]
[/ol]

So, it'd also be good to know if there is any automated way to get around the latter item, which I could mix into this, or else a way that I could simply skip those items which could be problematic in that way.

Thanks for any thoughts or suggestions

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[mmogul]

Here some code I have that might be a start. I commented out an ErrorHandler function I use to record errors.
You may have to change the mdb suffix to accdb. I haven't used this in a while but I believe it is good code.

Function CompactExternalDatabase(sSourceFile As String, Optional sDestinationFile As String) As Boolean
'***
'Purpose:  Runs a compact and repair on the submitted database. If the user
'           submits a destination database, then the result file will be the
'           the destination name and the original file is not deleted.  Otherwise, the compacted file will maintain the
'           same name
'           The parameter sent should not have the mdb suffix.  This will be added by the procedure
'CalledFrom: Various
'Calls  : basErrorHandler.ErrorHandler()
'
'Assumes:
'Written: MM
'Updated:
'Attribu: na
'Returns: True if process is ok

Dim bErr As Boolean
Dim bDestination As Boolean
On Error GoTo ErrorHandling:
CompactExternalDatabase = False

If sDestinationFile = "" Or sSourceFile = sDestinationFile Then
    sDestinationFile = sSourceFile & "_new"
    
Else
    bDestination = True
End If


If Right(sSourceFile, 4) = ".mdb" Then
Else
    sSourceFile = sSourceFile & ".mdb"
End If

If Right(sDestinationFile, 4) = ".mdb" Then
Else
    sDestinationFile = sDestinationFile & ".mdb"
End If

sDestinationFile = sDestinationFile & ".mdb"


Application.CompactRepair sSourceFile, sDestinationFile

'Delete old database
If bDestination Then
Else
    Kill sSourceFile
    
'** Rename temporary database to original name unless sDestination was sent
    Name sDestinationFile As sSourceFile
End If

CompactExternalDatabase = True

ExitProc:
  Exit Function
    
ErrorHandling:
  Select Case Err.Number
    Case Else
     '   Call ErrorHandler("basUtils.CompactExternalDatabase", Err.Number, Err.Description)
        GoTo ExitProc
  End Select

End Function

 

[kjv1611]

Yeah, that seems like it would work. However, how do you test with that code as to whether the newly compacted and repaired database actually ended up with a corrupted database? That's basically what I want to protect against.. the main thing, anyway. I don't want to create one problem by fixing another.

Also, if I could find a way to decompile - compact/repair - compile - compact/repair, that'd be excellent. I think I can sort it out, b/c I think I could basically pass a command to the SHELL() function to open each with the proper piece switch for decompiling, and then open the database normally, and run a compile command from the vb editor - I think that's possible - and THEN doing a final compact/repair.

I mention adding in the decompile piece, since that seems to fix all sorts of additional bugs that creep into databases over time (Not all databases, all the time, mind you).

Anyway, thanks for sharing, mmogul.

If I do get something sorted out that I think is safe enough for me to use in my environment, I'll be sure to post it back here. Right now, this is more of an idea/concept for which I am unsure whether I'll have enough time available to do it justice.



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Knicks]

You can use a switch to open the database /decompile. I had thought about doing something like this when I used to manage a ton of databases. I believe once it's opened using the switch it remains decompiled until it is recompiled. Read on though,

I think you need to think of this perhaps more globally. First, front-end DBs that have code and forms should be kept on the network in their gold untouched state (fully compacted and compiled). When a user accesses this gold file it should be through a batch file that copies to somewhere they can use it on the network or hard drive and then opens the latest gold copy from network. No need for the decompile / compile on the front end. They get a clean file everytime. And the most updated front end!

Okay, on to the 2nd portion. Yes the backend needs to compacted but should have no code to compile decompile. I would loop through the network for these back-end files and make sure there is no LDB open. Also while looping make a backup copy somewhere on the network.


1. Copy
2. Check for LDB (I've seen systems fail trying to mess with open file)
3. perform compact as above type code

Done,
I think that is the simplest technique. There may be better more complicated options.

 

[kjv1611]

Thanks for that additional note. That is something to think about going forward... have them all access the same front-end copy, and that front end just basically:
1. copies self to their local drive
2. closes self
3. opens new copy from their local drive..

So always new, always latest... yeah, that I'll have to remember.. hopefully build into a template for future projects.



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57