I have a database which controls the operation of 15 others. At times the remote databases grow too large and need compacting. Rather than doing this manually, I want to programme the master database to firstly compact the remote databases and then repopulate them. One can use Application.CompactRepair to do the first stage, but this demands a new name for the compacted database. I can then use KILL to delete the original database, but I cannot find a way of renaming the compacted database with the original name. Would a Shell command work here? Can anyone suggest another way to do this?

Thanks

I know you can do this in two steps from Access code. The first is to copy the file with the name you want, and the second is to delete the old file:

'-----
Set WshShell = WScript.CreateObject("WScript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")

objFSO.CopyFile "C:\YourOldFileName.mdb", "C:\YourNewFileName.mdb", True
objFSO.DeleteFile("C:\YourOldFileName.mdb")
'-----

The "True" boolean is to overwrite an existing file with the same name.

HTH


mmcdonal

Do you need a reference to some Shell Object or Scripting command in MS Access?


Database Agreements

Sorry for the delay:

You will need to declare some items if you have option explicit. I generally do the following and end up with Variants:

Dim WshShell
Dim objFSO

Notice I don't "type" the variables (e.g. "As String," "As Long" etc.)

Otherwise it works with MDAC and WMI. Are you having any problems using this?



mmcdonal

Sorry, but I cannot get this to work. I have tried 'Dim'-ing the variables as either nothing ( = a Variant), directly as a Variant, and even as an Object, but the programme fails to recognise the commands. I have also tried adding the 'Microsoft Script Control 1.0' to my references [the only one that looked even remotely relevant] with no effect. There must be something else I'm missing. Any thoughts?

Thanks for trying

Sorry, slight modification. I got this to work on a button click:

'-----
Private Sub Command0_Click()

Dim objFSO

Set objFSO = CreateObject("Scripting.FileSystemObject")

objFSO.CopyFile "C:\YourOldFileName.mdb", "C:\YourNewFileName.mdb", True
objFSO.DeleteFile ("C:\YourOldFileName.mdb")

End Sub
'-----

HTH

mmcdonal

Works perfectly. Thanks very much, you have no idea how much this helps.

Spikey

Hmm.
I wonder what happens if the .mdb is locked.
Do you get an Error and would you deal with it gracefully?


Database Agreements