Excel to repair Access database

[adder]

Not sure whether I should post this in the Access section, but here's what I need - creat a macro in excel to compact 2 Access dbs (Risk.mdb and Riskdata.mdb)

This is a daily task for me, for now I'm doing it mannually, open each database, and go Tools --> database utilities --> compact and repair database.

I've thought about using macro within each database - whenever opening the file it triggers repair automatically, however I don't want to do it every time I open the database, instead I wish to control the repair from a master excel file.

Thanks for your help in advance

 

[Ruddles]

If you do want code that opens, compacts and repairs then closes why not set the database to compact and repair on close?

Is it possible to set that option via VBA?

In which case the OP could (programatically) open the database, set the option, close the database, re-open it, unset the option and close it again.

If that's what he wanted to do...

 

[Norie]

Ruddles

It's a one-off setting, why do it programatically?

Are the databases never going to be opened manually?

 

[xenou]

Lightly tested, but Ruddle's code does work in Excel 2003. I added lines to move the file so we can compact it back into its original file name/path.

[COLOR="Navy"]Sub[/COLOR] CompactACDB()
[COLOR="Navy"]Dim[/COLOR] FSO [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR]
[COLOR="Navy"]Dim[/COLOR] AC [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR]
[COLOR="Navy"]Dim[/COLOR] sTempFilePath [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] sDatabaseFilePath [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]

    [COLOR="SeaGreen"]'//Access Database to Compact[/COLOR]
    sDatabaseFilePath = "C:\myTemp\TestDB5.mdb"
    
    [COLOR="SeaGreen"]'//Rename Database[/COLOR]
    [COLOR="Navy"]Set[/COLOR] FSO = CreateObject("Scripting.FileSystemObject")
    [COLOR="Navy"]With[/COLOR] FSO
        sTempFilePath = .GetTempName
        sTempFilePath = Replace(sTempFilePath, "tmp", "mdb")
        sTempFilePath = .GetParentFolderName(sDatabaseFilePath) & "\" & sTempFilePath
        .MoveFile sDatabaseFilePath, sTempFilePath
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
    
    [COLOR="SeaGreen"]'//Compact Database (compacts back into its original file location)[/COLOR]
    [COLOR="Navy"]Set[/COLOR] AC = CreateObject("Access.Application")
    AC.DBEngine.CompactDatabase sTempFilePath, sDatabaseFilePath
    
My_Exit:
[COLOR="SeaGreen"]'//Close Access[/COLOR]
[COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] AC [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
    AC.Quit
    [COLOR="Navy"]Set[/COLOR] AC = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="SeaGreen"]'//If original file was renamed and failed to compact then restore it[/COLOR]
[COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] FSO.FileExists(sDatabaseFilePath) [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] FSO.FileExists(sTempFilePath) [COLOR="Navy"]Then[/COLOR]
        FSO.MoveFile sTempFilePath, sDatabaseFilePath
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
FSO.DeleteFile (sTempFilePath)
[COLOR="Navy"]Set[/COLOR] FSO = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]

ErrHandler:
[COLOR="Navy"]Resume[/COLOR] My_Exit

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

BTW I don't use or like compact on close myself. I am in and out of some databases several times every day that would take 20-30 seconds to compact - that's too long to be looking at a progress bar multiple times each day (and for that matter, I have databases that I don't open manually except on rare occasions ...).

I've never found a way to set compact on close programmatically ... you'd think it would be easy to find under the Application object. Also, by the way, the database must not be in use. The above code actually handles this since the file can't be moved if it's in use, so no temp file created and therefore no compact occurs either.

 

[Ruddles]

It's a one-off setting, why do it programatically?

Are the databases never going to be opened manually?

I thought that was what the OP was looking for. I wouldn't want to leave the option enabled for the reason Xenou stated: if you're in and out of these database several times a day you'd end up staring at a blank screen much too often.

However as the code appears to work in 2003, that's not an issue.

 

[Norie]

There's also the slight chance that doing something like this programatically could increase the chance of the database becoming corrupt.

Emphasis on the slight.

 

[Ruddles]

That would worry me. I think I would keep a backup just in case.

 

[xenou]

Would (and does) worry me. Although I can report that I have been compacting databases via similar code for close to a year now with never a hiccup.

This is a quick edit to save the original database with a timestamp (though it will litter the folder with many backups in time ... one might put the backups/temp files in a subfolder or backup folder instead).

[COLOR="Navy"]Sub[/COLOR] CompactACDB()
[COLOR="Navy"]Dim[/COLOR] FSO [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR]
[COLOR="Navy"]Dim[/COLOR] AC [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR]
[COLOR="Navy"]Dim[/COLOR] sTempFilePath [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] sDatabaseFilePath [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]

    [COLOR="SeaGreen"]'//Access Database to Compact[/COLOR]
    sDatabaseFilePath = "C:\myTemp\TestDB5.mdb"
    
    [COLOR="SeaGreen"]'//Rename Database[/COLOR]
    [COLOR="Navy"]Set[/COLOR] FSO = CreateObject("Scripting.FileSystemObject")
    [COLOR="Navy"]With[/COLOR] FSO
        sTempFilePath = .GetBaseName(sDatabaseFilePath)
        sTempFilePath = sTempFilePath & "_" & Format(Now, "yyyymmdd.hhnnss")
        sTempFilePath = sTempFilePath & "." & .GetExtensionName(sDatabaseFilePath)
        sTempFilePath = .GetParentFolderName(sDatabaseFilePath) & "\" & sTempFilePath
        .MoveFile sDatabaseFilePath, sTempFilePath
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
    
    [COLOR="SeaGreen"]'//Compact Database (compacts back into its original file location)[/COLOR]
    [COLOR="Navy"]Set[/COLOR] AC = CreateObject("Access.Application")
    AC.DBEngine.CompactDatabase sTempFilePath, sDatabaseFilePath
    
My_Exit:
[COLOR="SeaGreen"]'//Close Access[/COLOR]
[COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] AC [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
    AC.Quit
    [COLOR="Navy"]Set[/COLOR] AC = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="SeaGreen"]'//If original file was renamed and failed to compact then restore it[/COLOR]
[COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] FSO.FileExists(sDatabaseFilePath) [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] FSO.FileExists(sTempFilePath) [COLOR="Navy"]Then[/COLOR]
        FSO.MoveFile sTempFilePath, sDatabaseFilePath
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]Set[/COLOR] FSO = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]

ErrHandler:
[COLOR="Navy"]Resume[/COLOR] My_Exit

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

 

[Ruddles]

This is a quick edit to save the original database with a timestamp (though it will litter the folder with many backups in time ... one might put the backups/temp files in a subfolder or backup folder instead).

And delete them when they're a month (or whatever period) old.

I'm not asking you to code this, just suggesting a method of keeping the backups under control.

 

[xenou]

I hear you. I've actually been tweaking a more comprehensive procedure for the last few weeks - though (honestly) it's almost embarassing how much code I've written when you can see that 20 lines (as above) will handle the basic task. Pretty much everything is validated, checked, and logged - for the purpose of compacting one or more databases after hours during the night (I actually compact around 10 databases, on a weekly schedule). But on the bright side, the code is embedded in a class file so all you need to use the class is just a few statements ... if you think you might find it useful let me know.

 

[adder]

Thanks Ruddles and Xenou, I think I'll try Ruddle's code first.

Norie, I almost never need to open the 2 mdb files other than for the purpose of compact and repair, that's why I was not into c/r on close setting.

Here's the thing, every day around 4pm I run a company proprietary program, which interacts with the 2 mdb at the back end (without opening them). I guess it runs dumping-data/updating-table type of things.

If I don't c/r mdb for some while, the program running soon becomes very slow so I often c/r them mannually right before running the program, like I said I was lazy to open them daily just to clean up.

Then there're other excel/access files used to extract data from the 2 risk database, again, I never need to directly open the 2 database. Once useful data is extracted/processed (mostly in a few excel spreadsheets, which are saved and stored), info in the 2 database is no longer needed so they could be compacted again for the next time update.