Compact an MDE file through ADO (1 Viewer)

[RCurtin]

Compact an MDE file through DAO

Hi everyone,
I've figured out the best way for me to compact my database from the posts here but I have a small problem with it. My backend end is an MDE file (have no idea why I did that now) and the code I have doesn't seem to work with MDE files.

Dim RS As DAO.Recordset, DB As DAO.Database
    Dim NewDBName As String, DBName As String
    Set DB = CurrentDb()
    Set RS = DB.OpenRecordset("DBNames")
    On Error Resume Next
    RS.MoveFirst
    Do Until RS.EOF
        DBName = RS("DBFolder") & "\" & RS("DBName")
        
        Debug.Print "original: " & DBName
        ' Create a new name for the compacted database.
        ' This example uses the old name plus the current date.
        NewDBName = Left(DBName, Len(DBName) - 4)
        NewDBName = NewDBName & " " & Format(Date, "MMDDYY") & ".mdb"
        
        Debug.Print "newname: " & NewDBName
        DBEngine.CompactDatabase DBName, NewDBName
        RS.MoveNext
       Loop
    ' Close the form, and then close Microsoft Access
    DoCmd.Close acForm, "CompactDB", acSaveYes
    RS.Close
  '  DoCmd.Quit acSaveYes

Is it possible to compact an mde file through DAO?? I'd rather not change the BE to an mdb file now because I would need to import everything into a new DB and then secure it again.

Thanks,
Rcurtin.

 

[RCurtin]

Sorry that should be DAO

OK,
I've just discovered that the code I have will work with an mde file. The mde file I was using to test it was secure so.. my question now is how do I compact a secure mde file through DAO?

 

[RCurtin]

Compact and backup a database automatically - working

I finally got it to work! The code now compacts the database and saves a copy of the compacted file in another folder. I created a database with a form and the following is the code in the form load event. Then I've set scheduled task using windows scheduler to open and run the database at midnight every night so the database is backed up and compacted automatically.

I wasn't able to get it to working using the DBEngine.compactDatabase DAO method. I couldn't find out where to supply the workgroup file..

However it is now working using the JRO method (JetEngine.CompactDatabase)

Private Sub Form_Load()
On Error GoTo Err_Form_Load

    Dim CompactedDB As String
    Dim dbName, sBackupPath, sBackupFile As String
    Dim fso As FileSystemObject
    Dim DB As DAO.Database
    Dim RS As DAO.Recordset
    Dim JRO As JRO.JetEngine
    
    Set JRO = New JRO.JetEngine
    Set DB = CurrentDb()
    Set RS = DB.OpenRecordset("DBNames")

    RS.MoveFirst
    Do Until RS.EOF

        dbName = RS("DBFolder") & RS("DBName")
        ' Create a new name for the compacted database.
        CompactedDB = Left(dbName, Len(dbName) - 4)
        CompactedDB = CompactedDB & Format(Date, "MMDDYY") & ".mde"
         
        JRO.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbName & ";User Id= realAdmin; Password =xxxxxx; Jet OLEDB:System Database = C:\DrawingRegister\DrawingsWorkGroup.mdw", _
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CompactedDB & ""
        
            'make backup of compacted file
        sBackupPath = RS("DBBackupPath")
        Set fso = New FileSystemObject
        fso.CopyFile CompactedDB, sBackupPath & "BackupDrawingsDB" & ".mde", True 'copy file that has been compacted into backup folder
        Set fso = Nothing
        
        'delete old database
        If Dir(dbName) <> "" Then Kill (dbName)
        
        'rename temporary database to original name
        Name CompactedDB As dbName
        RS.MoveNext
    Loop
    
    ' Close the form, and then close Microsoft Access
    DoCmd.Close acForm, "CompactDB", acSaveYes
    RS.Close
    DoCmd.Quit acSaveYes
    
    
Exit_Form_Load:
    Exit Sub
    
Err_Form_Load:
    Debug.Print Err.Number & " - " & Err.Description
    Resume Exit_Form_Load
End Sub

Hope this is of use to someone else.

 

[MsfStl]

Can this be used in a macro? I have a merge utility that I have created for a couple of data entry dbs. When my data entry peeps finish for the day I have them click the transfer (merge) data button on their Db menu form and viola their data is transferred and merged to a main Db on the server. Presently, once or twice a week I go through and backup both their Dbs and the Main Db, then I wipe their Db so there is no data held on their C: drive. I would very much like to create a macro that would automate that process for me, then I could spend my time validating their work and rather than cleaning DB house every couple of days. Any ideas on how I could go about this? FYI, I do not have remote access to their 'puters so it has to be done at the user level.

Thanks,
~S

 

[MsfStl]

Can this be used in a macro? I have a merge utility that I have created for a couple of data entry dbs. When my data entry peeps finish for the day I have them click the transfer (merge) data button on their Db menu form and viola their data is transferred and merged to a main Db on the server. Presently, once or twice a week I go through and backup both their Dbs and the Main Db, then I wipe their Db so there is no data held on their C: drive. I would very much like to create a macro that would automate that process for me, then I could spend my time validating their work and rather than cleaning DB house every couple of days. Any ideas on how I could go about this? FYI, I do not have remote access to their 'puters so it has to be done at the user level.

Thanks,
~S

 

[RCurtin]

To be honest I have never really used macros so maybe someone else could answer that. However you don't need to change much of the code to modify it for yourself.
You just need a table called DBNames with the following fields:
DBID
DBFolder
DBName
DBBackupPath

Put the pathnames, filenames and backup paths of your databases in the tavle and it should be fine. As I said you can use Windows Task Scheduler to run it at a specific time - this site has a tutorial on using Task Scheduler:
http://www.iopus.com/guides/winscheduler.htm

You will need to change the following line to include your username, password and workgroup name if the database is secured:

JRO.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbName & ";User Id= realAdmin; Password =xxxxxx; Jet OLEDB:System Database = C:\DrawingRegister\DrawingsWorkGroup.mdw", _
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CompactedDB & ""

If its not secure - just leave out the username, password and workgroup name .

Hope that helps,
R.

 

[RCurtin]

Automatic Repair and Backup

Hi,
I have been working on this more recently so thought I would post the code as it is now. It may help someone else:

Option Compare Database
Option Explicit

'Needs reference to Microsoft Scripting runtime
'Needs reference to Microsoft Jet and Replication Objects
'If a database is closed
'   A new copy of it is made (database name+date)
'   The new copy is compacted
'   The compacted database is backed up
'   The original uncompacted version is deleted


Private Sub Form_Load()
On Error GoTo Err_Form_Load

    Dim CompactedDB As String
    Dim dbPathName, dbFileName, sBackupPath, sBackupFile, workGroupFile, ldbFile, DBPass As String
    Dim fso As New FileSystemObject
    Dim DB As DAO.Database
    Dim RS As DAO.Recordset
    Dim JRO As JRO.JetEngine
    
    Set JRO = New JRO.JetEngine
    Set DB = CurrentDb()
    Set RS = DB.OpenRecordset("DBNames")
    DBPass = "xxxxxxx"
    RS.MoveFirst
    Do Until RS.EOF
        dbFileName = RS("DBName")
        dbPathName = RS("DBFolder") & dbFileName
        ' Create a new name for the compacted database.
        CompactedDB = Left(dbPathName, Len(dbPathName) - 4)
        CompactedDB = CompactedDB & Format(Date, "MMDDYY") & ".mde"
        workGroupFile = RS("Workgroup")
        ldbFile = Left(dbPathName, Len(dbPathName) - 3) & "ldb"
        
        If Dir(ldbFile) <> "" Then   'db is open
            Me.txtProgress.Value = "The database file " & dbPathName & " is open - creating backup.."
            Debug.Print "A user has this file open"                                   '
            sBackupPath = RS("DBBackupPath")
            Set fso = New FileSystemObject
                '   backup un-compacted file
            fso.CopyFile dbPathName, sBackupPath & dbFileName, True 'copy file that has been compacted into backup folder
            Set fso = Nothing
        Else                    'file is not open - can be compacted
        
            Me.txtProgress.Value = "Compacting and repairing the file: " & dbPathName & ""
            JRO.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPathName & ";User Id= realAdmin; Password =" & DBPass & "; Jet OLEDB:System Database =" & workGroupFile & "", _
            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CompactedDB & ""
            sBackupPath = RS("DBBackupPath")
            Set fso = New FileSystemObject
                'back up compacted file
            fso.CopyFile CompactedDB, sBackupPath & dbFileName, True 'copy file that has been compacted into backup folder
            Set fso = Nothing
            'delete old database
            If Dir(dbPathName) <> "" Then Kill (dbPathName)
            
            'rename temporary database to original name
            Name CompactedDB As dbPathName
        End If
                  
        RS.MoveNext
    Loop
    Me.txtProgress.Value = "Done"
    ' Close the form, and then close Microsoft Access
    DoCmd.Close acForm, "CompactDB", acSaveYes
    RS.Close
    Set RS = Nothing
    DoCmd.Quit acSaveYes
    
    
Exit_Form_Load:
    Exit Sub
    
Err_Form_Load:
If Err.Number = -2147467259 Then        'a user has database open or workgroup name is incorrect
    Debug.Print "A user has the database open"
    Resume Next
Else
    Debug.Print Err.Number & " - " & Err.Description
    Resume Exit_Form_Load
End If
End Sub

So I'm using the Windows Task Scheduler to run this every night.

If a database is open, it just backs it up, otherwise it compacts it and backs it up.

 

[IpeXeuS]

Great examples guys, these was exactly what I looking for.

 

[sweetmail]

I have a Access database (PromotionalEmail.mdb) used to send out thousands of email at one time using microsoft Outlook.

I just run the compact and repair manually once a while and use copy and paste the closed PromotionalEmail.mdb database.

Now I am looking into automating the compact and repair the database and then backing it up immediately. I search the web and came to this thread.

According to this thread, I think I need to create a new database with only a blank form. I will call it BackupPromotionalEmail.mdb and the form name is frmbackup

Can anyone help me with a questions? Thanks in advance

1) I create the table DBNames with the following fields:
DBID
DBFolder = L:\Servers\
DBName = PromotionalEmails.mdb
DBBackupPath = L:\Servers\backup\

My question is, L is a network drive, is that OK?

Thank you for helping.

 

[Dudley]

Workgroup file?

Hi RCurtin,
I'm trying to use your system to manage my BE file and I'm running into trouble with the Workgroup file. I don't know if I even have one, so it's being blank in the table is causing a problem. Can you help me out?
Thanks!
-Dudley