Automating compact and backup

[Malcy]

Hi
I am trying to create an option to compact the BackEnd of a split database and also another option to back it up onto a CD-Writer.
Having looked at posts I have made a third Utility database (with no links to the BackEnd) and am trying to work out how some of the previous posts work but they are all doing slightly different things and I cannot follow the code.
Using MSAccess Help I have created the following line of code off a button in the Utility database
DBEngine.CompactDatabase "C:\DataFiles\MicaPharm\MgmMethBe.mdb", "C:\DataFiles\MicaPharm\MgmMcBeCompact.mdb"

This works fine in creating a new compacted file. Help then says (helpfully) you can delete the old file and rename the new file back to the old file name.
Since the idea is to try and simplify the user requirements, how can I do this programatically?
Also can I use a similar technique to write to CD-Rewriter which is disc D:\ ?
Any help most appreciated
All the best

Malcy

 

[MattS]

To delete the original use the wonderfully titled Kill command!

Dim strName1 As String ' Current name/location of DB
Dim strName2 As String ' New name/location of DB
    strName1 = "C:\DataFiles\MicaPharm\MgmMethBe.mdb"
    strName2 = "C:\DataFiles\MicaPharm\MgmMcBeCompact.mdb"
    DBEngine.CompactDatabase strName1, strName2
    Kill strName1
    Name strName2 As strName1

Not sure about writing to the cd-writer.

Matt.

 

[Malcy]

Thanks MattS for that.
Your code is a bit neater than mine!
Being a bit cautious should I copy the original file into a backup directory so that if the compact goes wrong then I can restore the original - or is that really too much like belt and braces?
Will have to search a bit deeper for the CD-Writer issue.
Best wishes

Malcy

 

[MattS]

It's always a good idea to have a back-up copy of a database. It might seem like 'belt and braces', but one day you'll be glad that you do it!!

 

[Malcy]

Thanks MattS
Came to that conclusion myself and have set it up.
Just (?!?) need to sort out copying to CD-Writer now but that looks like being much more complex - which strikes me as odd since surely there must be lots of people wanting to do that these days.
Best wishes

Malcy

 

[MattS]

I don't believe it will be that easy, if it's possible at all.

The only solution I can think of is to have Access open up whichever copying software it is that you use (i.e. Roxio, Nero etc..).

This would still require user interaction. If you wanted to fully automate the process of backing up to the cd drive - well, I haven't a clue!!

Matt.

 

[Malcy]

I'm using Windows XP to do the backup since a) it does it really well and b) it leaves the copy session open so you can effectively use the CD-ROM as a rewritable disc. Cunning eh!
Will keep on hunting.
Currently trying to explore FileSystemObject but fear I am going deep see diving with a snorkel!
Will see how it goes.
Best wishes

Malcy