Solved: Xcopy command

[mdmackillop]

At present I have an Xcopy routine which is stored on my C Drive, called via a shortcut. I'm trying to tidy up this programme.
[VBA]
stAppName = "c:\backsup\smonba~1.pif"
Call Shell(stAppName, 1)
[/VBA]

The code for Mon.bat is

xcopy S:\Database\Valuat~1.mdb C:\Database\Backup\thu\ /v/y
xcopy S:\Database\SasinesData.mdb C:\Database\Backup\Sasines\ /m/v/y

Can someone give me a "direct" coding, which avoids the need for the C Drive Bat file and shortcut?
MD

[Tommy]

Hi MD,
The sub SetClearArchiveBit is from the help file. Otherwise a filecopy should work. The revised one (smaller ) should work like the xcopy command in otherwords it sets the archive flag or removes it. Look like you need a reference to the MS scripting runtime also.
[VBA]
Private Sub Form_Load()
FileCopy "S:\Database\Valuat~1.mdb", "C:\Database\Backup\thu\Valuat~1.mdb"
FileCopy "S:\Database\SasinesData.mdb", "C:\Database\Backup\Sasines\SasinesData.mdb"
Call SetClearArchiveBit("C:\Database\Backup\Sasines\SasinesData.mdb")
End Sub

Sub SetClearArchiveBit(filespec)
Dim fs, f, r
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(fs.GetFileName(filespec))
If f.Attributes And 32 Then
r = MsgBox("The Archive bit is set, do you want to clear it?", vbYesNo, "Set/Clear Archive Bit")
If r = vbYes Then
f.Attributes = f.Attributes - 32
MsgBox "Archive bit is cleared."
Else
MsgBox "Archive bit remains set."
End If
Else
r = MsgBox("The Archive bit is not set. Do you want to set it?", vbYesNo, "Set/Clear Archive Bit")
If r = vbYes Then
f.Attributes = f.Attributes + 32
MsgBox "Archive bit is set."
Else
MsgBox "Archive bit remains clear."
End If
End If
End Sub

Sub SetClearArchiveBit(filespec)
Dim fs, f, r
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(fs.GetFileName(filespec))
If f.Attributes And 32 Then
f.Attributes = f.Attributes - 32
Else
f.Attributes = f.Attributes + 32
End If
End Sub

[/VBA]

[mdmackillop]

Thanks Tommy,
I think I was too hung up on trying to run Xcopy to consider alternative solutions. It seems so obvious now. I'll try it tomorrow, but I don't foresee any problems with this approach. Just for information, I use the procedure to back up a critical database stored on the server onto spare capacity on the user's hard disk, on the premise that if you have all the backups, you'll never need to use them!
MD

[Tommy]

MD,
Do you ever compact and repair? I have some code I picked up from MS to compact and repair an Access database. One of the features is the compacted database has to be renamed. Let me look for it, I'll modify it to fit your needs and post it so you can have it in case you need it.

I am debugging some translated apps so I am bored stiff.

[Tommy]

Just in case you need it
[VBA]
' this requires a reference to Microsoft Jet and Replication Objects 2.6 Library
Set JRO = New JRO.JetEngine
JRO.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=S:\Database\Valuat~1.mdb;", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Database\Backup\thu\Valuat~1.mdb;"
JRO.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=S:\Database\SasinesData.mdb;", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Database\Backup\Sasines\SasinesData.mdb;"
Call SetClearArchiveBit("C:\Database\Backup\Sasines\SasinesData.mdb")

[/VBA]

[mdmackillop]

Thanks Tommy,
The DB is one I created a few years ago, and has slightly different versions running in 6 offices. It's due an overhaul and an update, and as I know a bit more about manipulating VBA than I did when I created it, I'm trying to make it a bit more User Friendly (or Idiot Proof in less politically correct terms). Your Comact And Repair will help on that score, saving about 15mins per explanatory phone call!
MD

[Tommy]

Good deal. Glad I could help