Automate Compact and Repair Across Network 1

[perrymans]

We have many many users with many many public folders and subfolders across the network.

I want to identify a solution that will go out, find all .mdb files, and compact/ repair them, even in subfolders.

Is there some scriptiong or even a commercial product that would make this easy and trudtworthy?

Thanks. Sean.

 

[jadams0173]

I actually had this same problem/thought. I modified some VB 6 code that I found on the net that will find all the mdb's on a drive. Folders and subfolders alike. It will compact them all if a lock file (.ldb) is not present.



I tried to have patience but it took to long! -DW

 

[JerryKlmns]

perrymans
This thread deals with something similar.
Find word files for occurances of 1000 set strings thread707-1282763

jadams0173
What font color did you use, because I can 't see the url or the code. And believe me, I had my eyes checked recently!

 

[mp9]

It won't help with finding the MDBs but you may find the Handy Access Launcher provides an easy way of automating the compact/repair operation of known databases. You can get it from

http://www22.brinkster.com/accessory/resources/001.shtml

 

[BuilderSpec]

Hi

In VBA can you not use the filesearch routines to find all the mdb's, then again use the Access VBA routines to perform a compact and repair.



Hope this helps!

Regards

BuilderSpec

 

[jadams0173]

I didn't actually post a link becuase I couldn't remember where it came from and I apparently didn't mark it. Here is the code. After looking at it once again, it was orginally written to find a specfic file extensions (.txt, .xls etc) and add up the total space on a particular drive. I already had code to compact a database so I simply added the compact Call into the API function when it found an MDB file.
I put the code in the form where the command button is. All except the compact function.

Option Explicit
[blue]'Create a form with a command button (command1), a list box (list1)
'and four text boxes (text1, text2, text3 and text4).
'Type in the first textbox a startingpath like c:\
'and in the second textbox you put a pattern like *.* or *.txt
[/blue]
Private Declare Function FindFirstFile Lib "kernel32" Alias "FindFirstFileA" (ByVal lpFileName As String, lpFindFileData As WIN32_FIND_DATA) As Long
Private Declare Function FindNextFile Lib "kernel32" Alias "FindNextFileA" (ByVal hFindFile As Long, lpFindFileData As WIN32_FIND_DATA) As Long
Private Declare Function GetFileAttributes Lib "kernel32" Alias "GetFileAttributesA" (ByVal lpFileName As String) As Long
Private Declare Function FindClose Lib "kernel32" (ByVal hFindFile As Long) As Long

Const MAX_PATH = 260
Const MAXDWORD = &HFFFF
Const INVALID_HANDLE_VALUE = -1
Const FILE_ATTRIBUTE_ARCHIVE = &H20
Const FILE_ATTRIBUTE_DIRECTORY = &H10
Const FILE_ATTRIBUTE_HIDDEN = &H2
Const FILE_ATTRIBUTE_NORMAL = &H80
Const FILE_ATTRIBUTE_READONLY = &H1
Const FILE_ATTRIBUTE_SYSTEM = &H4
Const FILE_ATTRIBUTE_TEMPORARY = &H100

Private Type FILETIME
    dwLowDateTime As Long
    dwHighDateTime As Long
End Type

Private Type WIN32_FIND_DATA
    dwFileAttributes As Long
    ftCreationTime As FILETIME
    ftLastAccessTime As FILETIME
    ftLastWriteTime As FILETIME
    nFileSizeHigh As Long
    nFileSizeLow As Long
    dwReserved0 As Long
    dwReserved1 As Long
    cFileName As String * MAX_PATH
    cAlternate As String * 14
End Type
Function StripNulls(OriginalStr As String) As String
    If (InStr(OriginalStr, Chr(0)) > 0) Then
        OriginalStr = Left(OriginalStr, InStr(OriginalStr, Chr(0)) - 1)
    End If
    StripNulls = OriginalStr
End Function

Function FindFilesAPI(path As String, SearchStr As String, FileCount As Integer, DirCount As Integer)
[red]
    'KPD-Team 1999
    'E-Mail: KPDTeam@Allapi.net
    'URL: [URL unfurl="true"]http://www.allapi.net/[/URL]
[/red]
    Dim FileName As String ' Walking filename variable...
    Dim DirName As String ' SubDirectory Name
    Dim dirNames() As String ' Buffer for directory name entries
    Dim nDir As Integer ' Number of directories in this path
    Dim i As Integer ' For-loop counter...
    Dim hSearch As Long ' Search Handle
    Dim WFD As WIN32_FIND_DATA
    Dim Cont As Integer
    
    If Right(path, 1) <> "\" Then path = path & "\"
    ' Search for subdirectories.
    nDir = 0
    ReDim dirNames(nDir)
    Cont = True
    hSearch = FindFirstFile(path & "*", WFD)
    If hSearch <> INVALID_HANDLE_VALUE Then
        Do While Cont
        DirName = StripNulls(WFD.cFileName)
        ' Ignore the current and encompassing directories.
        If (DirName <> ".") And (DirName <> "..") Then
            ' Check for directory with bitwise comparison.
            If GetFileAttributes(path & DirName) And FILE_ATTRIBUTE_DIRECTORY Then
                dirNames(nDir) = DirName
                DirCount = DirCount + 1
                nDir = nDir + 1
                ReDim Preserve dirNames(nDir)
            End If
        End If
        Cont = FindNextFile(hSearch, WFD) 'Get next subdirectory.
        Loop
        Cont = FindClose(hSearch)
    End If
    ' Walk through this directory and sum file sizes.
    hSearch = FindFirstFile(path & SearchStr, WFD)
    Cont = True
    If hSearch <> INVALID_HANDLE_VALUE Then
        While Cont
            FileName = StripNulls(WFD.cFileName)
            If (FileName <> ".") And (FileName <> "..") Then
                FindFilesAPI = FindFilesAPI + (WFD.nFileSizeHigh * MAXDWORD) + WFD.nFileSizeLow
                FileCount = FileCount + 1
                List1.AddItem path & FileName
      [blue]      'HERE IS WHERE THE COMPACT HAPPENS[/blue]
                Call CompactDB(path & FileName, "c:\temp.mdb", "")
                DoEvents
                
                
            End If
            Cont = FindNextFile(hSearch, WFD) ' Get next file
        Wend
        Cont = FindClose(hSearch)
    End If
    ' If there are sub-directories...
    If nDir > 0 Then
        ' Recursively walk into them...
        For i = 0 To nDir - 1
            FindFilesAPI = FindFilesAPI + FindFilesAPI(path & dirNames(i) & "\", SearchStr, FileCount, DirCount)
'        i = 1
        Next i
    End If
End Function

Private Sub Command1_Click()
    Dim SearchPath As String, FindStr As String
    Dim FileSize As Long
    Dim NumFiles As Integer, NumDirs As Integer
    Screen.MousePointer = vbHourglass
    List1.Clear
    SearchPath = Text1.Text
    FindStr = Text2.Text
    FileSize = FindFilesAPI(SearchPath, FindStr, NumFiles, NumDirs)
    Text3.Text = NumFiles & " Files found in " & NumDirs + 1 & " Directories"
    Text4.Text = "Size of files found under " & SearchPath & " = " & Format(FileSize, "#,###,###,##0") & " Bytes"
    Screen.MousePointer = vbDefault

End Sub

The compact function uses JRO so make reference to the Jet Replication Object in the references.
Put this in a modual.

Option Explicit

'---------------------------------------------------------------------------
'----
' PROCEDURE  : CompactDB
' DESCRIPTION :
' RETURN VALUE: true or false depending wether there were errors or not
' NOTE    :
'  Add "Microsoft Jet and Replication Objects 2.x Library" to
'  the references of your project
' EXAMPLE   :
'---------------------------------------------------------------------------
'----
Public Function CompactDB(pFile As String, pTemp As String, pPassword As String)
On Error GoTo ErrH
'pFile will be txtDBPath(x)
'pPassword will be txtdbPassword(x) if a password is given
'ptemp will be temp database name

Dim connJRO As JRO.JetEngine
Dim ConnstringSorg As String, ConnstringDest As String
Dim fso As FileSystemObject

Set connJRO = New JRO.JetEngine


' Ensure file is not read only
SetAttr pFile, vbNormal
ConnstringSorg = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & pFile & ";" & _
                "Jet OLEDB:Database Password=" & pPassword & ";"
  
ConnstringDest = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
  pTemp & ";Jet OLEDB:Engine Type=5" & _
  ";Jet OLEDB:Database Password=" & pPassword
  
  
  
  
'compact and kill the file
Screen.MousePointer = vbHourglass
'Err.Raise 61

Backup_File:
'Backup the DB about to be compacted in case a fatal error happpens you won't lose it!
'FileCopy pFile, "C:\Temp\Last.mdb"

Compact:
connJRO.CompactDatabase ConnstringSorg, ConnstringDest 'Compact the DB
Kill pFile 'Kill (delete) the source db
FileCopy pTemp, pFile 'Copy the newly compacted DB back as the original DB
Kill pTemp 'kill the temporary db
Screen.MousePointer = vbDefault


CompactDB = "OK"
Exit Function

I tried to have patience but it took to long! -DW