Compacting a different database 2

[AlexCuse]

Hi Guys,

I have a work order database sitting on a Citrix server that is accessed by multiple people at the same time. Because of this, I can't really set the 'Compact on close' option.

However, this has been growing rather large on me. I am trying to write something in either access or vbScript that I can schedule to run during off hours to compact and repair this database. Any ideas on how I can accomplish this? I feel like I've seen something in here before on this, but I can't seem to find it.

THanks,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[SgtJarrow]

Here is some older Access code I had to do just this. I think this is Access 2000 and newer, but I can't remember and I didn't comment the code for version information. You might also want to remove the msgbox calls if you plan to run this via a scheduled tasks as these will pause the code.

Public Function vntRepDB()

'**********************************************
' vntRepDB accepts a database name and path, for example:
' "C:\Path\DB.MDB"
' vntRepDB repairs the specified database if it is not open.
'**********************************************
    
    ' Hide all the warnings
    DoCmd.SetWarnings False
    
    ' Variables
    Dim strDBPathName As String
    strDBPathName = "G:\NewYorkBilling\Tina\Investigations\Clearstream\clearstream.mdb"
    
    ' Initiate error handling routine
    On Error GoTo err_vntRepDB
    
    ' Repair the database using the RepairDatabase method
    DBEngine.RepairDatabase strDBPathName
    
    ' If the database is repaired successfully, vntRepDB will return a successful response of "Pass"
    vntRepDB = "Pass"
    Exit Function
    
err_vntRepDB:
    ' Error routine handles any attempt to repair an open database and additional unexpected errors.
    Select Case Err.Number
        Case 3356
            MsgBox Err.Description & vbLf & "Please close all instances of the database and try again."
        Case Else
            MsgBox Err.Description
    End Select

    ' If an error occurred, the vntRepDB function returns user defined run-time error 65535.
    vntRepDB = CVErr(65535)

End Function

Public Function vntComDB()

'**********************************************
' vntComDB accepts a database name and path, for example:
' "C:\Path\DB.MDB"
' vntComDB compacts the specified database if it is not open.
'**********************************************
    
    ' Hide all the warnings
    DoCmd.SetWarnings False
    
    ' Variables
    Dim strDBPathName As String, strTempName As String
    strDBPathName = "G:\NewYorkBilling\Tina\Investigations\Clearstream\clearstream.mdb"
    
    ' Initiate error handling routine
    On Error GoTo err_vntComDB

    ' Create a temporary database using a random number in the Windows Temp directory.  The input database
    ' will be compacted into this temporary database.  The Environ Function returns the setting for the
    ' specified operating system variable.
    Randomize
    strTempName = Environ("Temp") & "\TempDB" & Int((99 * Rnd) + 1) & ".MDB"
    
    ' Compact the databse into a temporary database.
    DBEngine.CompactDatabase strDBPathName, strTempName
    
    ' If the database is compacted successfully, delete the original database and rename the temporary
    ' database to the original database's name.
    Kill strDBPathName
    Name strTempName As strDBPathName

    ' If the database is compacted successfully, vntComDB will return a successful response of "Pass".
    vntComDB = "Pass"
    Exit Function
    
err_vntComDB:
    ' Error routine handles any attempt to compact an open database and additional unexpected errors.
    Select Case Err.Number
        Case 3356
            MsgBox Err.Description & vbLf & "Please close all instances of the database and try again."
        Case Else
            MsgBox Err.Description
    End Select

    ' If an error occurred, the vntRepDB function returns user defined run-time error 65534.
    vntComDB = CVErr(65534)

End Function

Public Function CompactRepair()

'**********************************************
' CompactRepair accepts a database name and path, for example:
' "C:\Path\DB.MDB"
' CompactRepair calls the repair and compact procedures vntRepDB and vntComDB
'**********************************************
    
    ' vntRepReturn and vntComReturn store the results of teh vntRepDB and vntComDB procedures.
    Dim vntRepReturn As Variant, vntComReturn As Variant
    
    ' Hide all the warnings
    DoCmd.SetWarnings False
    
    ' Delete the lock file if it exists.
    If Dir("G:\NewYorkBilling\Tina\Investigations\Clearstream\clearstream.ldb") = "clearstream.ldb" Then
        Kill "G:\NewYorkBilling\Tina\Investigations\Clearstream\clearstream.ldb"
    End If
    
    ' Execute the vntRepDB procedure
    vntRepReturn = vntRepDB()
    
    ' Test for "Pass" or user-defined error.
    If IsError(vntRepReturn) Then
        MsgBox "Repair Failed" & vbLf & "Please Notify Robert Johnson @ x4-1945"
    Else
        MsgBox "Repair Successful"
    End If
    
    ' Execute the vntComDb procedure.
    vntComReturn = vntComDB()
    
    ' Test for "Pass" or user-defined error
    If IsError(vntComReturn) Then
        MsgBox "Compact Failed" & vbLf & "Please Notify Robert Johnson @ x4-1945"
    Else
        MsgBox "Compact Successful"
    End If

End Function

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[mp9]

Don't know if it works under Citrix but you might want to take at look at the Handy Access Launcher as an easy means of scheduling your database maintenance tasks, and more besides.

 

[AlexCuse]

mp9 - that is pretty cool! I don't think the powers that be would go for putting any kind of add-in program on this server though.

mstrmage1768 - I will give that a try some time in the next week and get back to you. Thanks!

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jadams0173]

Alex

I use JRO daily to compact multiple DB's at night. I have a very small VB6 app on my sceduled tasks and it runs at 2am and does the job.

Public Function CompactDB(pFile As String) As Boolean
On Error GoTo ErrH
Dim conn As New JRO.JetEngine
Dim ConnstringSorg As String, ConnstringDest As String

' Ensure file is not read only
SetAttr pFile, vbNormal
ConnstringSorg = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & pFile & ";" & _
                "Jet OLEDB:Database Password=word;"
  
ConnstringDest = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
  "G:\mypath\Temp.mdb; Jet OLEDB:Database Password=word:Engine Type=5;"
  

Screen.MousePointer = vbHourglass
conn.CompactDatabase ConnstringSorg, ConnstringDest


'compact and kill the file
Kill pFile
FileCopy "G:\mypath\Temp.mdb", pFile
Kill "G:\mypath\Temp.mdb"
Screen.MousePointer = vbDefault
Set conn = Nothing
CompactDB = True
Exit Function
ErrH:
Screen.MousePointer = vbDefault
Debug.Print Err.Description
End Function

I'm not at work but this is the jist of it. pFile is the db being compacted.

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

 

[mp9]

Alex - you can also install HAL on your PC, and run the scheduled tasks on your db from there as long as you can reference the db with the UNC path to its location on the Citrix server. Then, as long as you PC is switched on, the HAL task will run.

 

[AlexCuse]

Jadams - that was just the ticket, and allows me to do it from outside access like I wanted to. Thanks!

mstrmage - I think your code will come in handy too!

mp9 - I do like the looks of your HAL. It is a neat product

Thanks again guys

Ignorance of certain subjects is a great part of wisdom