VBA Vs Batch file for backup...I need some help??

[Mike375]

For some different reasons I would like to change using a batch file for backup puposes.

I do can everything with the date/time stamp and copying across network to other computers and external hatd drives. However, unlike the batch file the VBA I am using only works if the other computers and/or external hard drives are turned. The batch file will just by pass and copy to whatever is turned on. But the VBA is stopped with the Debug as soon as hits a computer/external hard drive not turned on.

Here are two of the copy lines, one going to an external drive and one going to a computer on the network. I would really like to get this working but to do so I have to get around the problem of one or more computers being turned off. I am assuming (hoping) there is some sort of IF or similar that will recognise a computer is not turned on and then move to the next copy line.

FileCopy "C:\ViperResides\Viper.mdb", "h:\ViperDailyj\Viper " + Format(Now, "YYYY-MM-DD") + " " & Format(Now, "hh-mm-ss") + ".mdb"

FileCopy "C:\ViperResides\Viper.mdb", "\\MikeCompaq\ViperDaily\Viper " + Format(Now, "YYYY-MM-DD") + " " & Format(Now, "hh-mm-ss") + ".mdb"

 

[namliam]

You could just use an "On error resume Next" to ignore any errors....

 

[Mike375]

Many thanks. Worked like a charm. Easy when you know how

Now that is fixed I am going to make a little continuous form and open/close and set value of a new record etc. to something like "Computer xyz done at Now() etc. Should be able to make it look better than the DOS screen while accomplishing the same result.

Thanks again

 

[namliam]

Just so you know, in case you dont ...
Err is the object that contains any information about errors.
i.e. Err.number or Err.Description

 

[Mike375]

Thanks again.

As a side note, that the thread where I was fiddling about saving Word docs and retrieving them and OLE etc, you were right, the linked OLE takes up the same sort of space as embedded. For what I had read I assumed linking to Word via OLE would be similar to linked tables. But it appears that it is embedded but is linked in terms of updating, both ways.

What caused me to check what was you had said plus it was taking just as long to make a link as to do the embedding. Then I made one DB with a table with OLE linked docs and another DB with the same docs embedded and the two DBs were about the same size.

Thus I moved to setting the HyperlinkAddress property with the field value.

No end to this stuff

 

[Mike375]

I have got

On Error GoTo Handler

FileCopy "C:\LettersCopy\CommFees.doc", "h:\ViperDailyj\CommFees " + Format(Now, "YYYY-MM-DD") + " " & Format(Now, "hh-mm-ss") + ".doc"
DoCmd.OpenForm "BUExp", acNormal, "", "", , acNormal
DoCmd.GoToRecord , "", acNewRec
Forms!BUExp!DiskBU = "Disk 1"

Exit Function

Handler:
DoCmd.OpenForm "BUExp", acNormal, "", "", , acNormal
DoCmd.GoToRecord , "", acNewRec
Forms!BUExp!DiskBU = "Disk 1 was turned off"
End Function

That all works nicely whether the disk is turned on or off (that is, puts the correct message in the form) but I need a module for each "disk" because if they are behind a label as one big block of code it stops at the second block of code when the disk is turned off. I read that it would not work but I could only get around it by putting each block for each computer in a function and calling it.

 

[namliam]

On Error GoTo Handler

FileCopy "C:\LettersCopy\CommFees.doc", "h:\ViperDailyj\CommFees " + DoCmd.OpenForm "BUExp", acNormal, "", "", , acNormal
DoCmd.GoToRecord , "", acNewRec
Format(Now, "YYYY-MM-DD") + " " & Format(Now, "hh-mm-ss") + ".doc"
Forms!BUExp!DiskBU = "Disk 1"


Exit Function

Handler:
Forms!BUExp!DiskBU = Forms!BUExp!DiskBU & " was turned off"
Resume Next 'this makes you return
End function

Resume next makes you return to the code, next line after the error.

 

[Mike375]

I might be misreading you as does your Exit Function mean that code is in a module?

What I have at the moment behind the button is

Call Disk1

Call Disk2

Call Disk3 etc. and that works fine ecept I hae to make amodule for each one.

But when I put the code that is contained in each module into a single block of code behind the button then only the first one works. Say the first two disks are turned off then it does the first one OK but stops at the the

FileCopy "C:\ViperResides\Viper.mdb", "h:\ViperDailyj\Viper " + Format(Now, "YYYY-MM-DD") + " " & Format(Now, "hh-mm-ss") + ".mdb"

One problem I can't solve with the code is to do a version of this part of batch file

copy \\MikeHP\ViperDaily\*.mdb \\MikeHP\ViperArchive
Del \\MikeHP\ViperDaily /Q

copy *.mdb \\MikeHP\ViperDaily

FileCopy (at least with what I do) requires the file name on the second part.

 

[namliam]

VBA native copy is more limited for sure...

Sub ...
code
Exit sub

error handling

End sub

(or replace sub with function)

You can do thise whereever you want, in a form or in a module or report.

 

[Mike375]

VBA native copy is more limited for sure...

My problem with copying the file is when the second part is copying something like Viper2008-12-03__14-04-04 and of course you don't what the exact name will be. But I might be able to get around it by using a similar thing to what I was doing with Word/Access. As you said before...Nothing is impossible

My main reason for wanting to use the code instead of batch files is that I want to avoid as much as possible having the DB install files on the person's computer and I make the "on screen" process look much better than the DOS screen. Much better to have "Computer XYZ is turned off" than the DOS/Batch file line. Even if I have to make different modules and call the functions that is still easier than typing the code out to create a big batch file.

 

[namliam]

Well... I think... this is what you are looking for...

Dir
FileSearch

You can use either function to read drives/folders/sub-folders/files like the DIR command on DOS. Using this you get back filenames which you can then use to "feed" your copy command much like Copy *.MDB

Sample from what I use for different purposes:

    Dim strFile     As String
    Dim i           As Integer
    Dim fs          As Object
    Dim strFolder   As String
    Dim strFileName As String
    
    Set fs = Application.FileSearch
    fs.newsearch
    fs.FileName = "*.*"
    fs.LookIn = myFolder ' Add a folder for testing.
    fs.SearchSubFolders = True
    fs.Execute
    For i = 1 To fs.FoundFiles.Count
        strFile = fs.FoundFiles(i)
debug.print strFile
        strFolder = Left(strFile, InStrRev(strFile, "\", -1))
Debug.print strFolder
        strFileName = Mid(strFile, Len(strFolder) + 1)
debug.print strFileName
    next I

Edit:
myFolder is a function I have in every database:

Function myFolder()
    myFolder = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\"))
End Function

This basicaly returns the folder the database is in.

 

[Mike375]

Thanks for that. As you said, Nothing is Impossible.

I will look what you have a bit later as I now have the luxury of time as I have it working, probably a bit crude but works and produced the "on screen progress" I want. I have left that part out

The DB is in folder ViperResides. The date/time stamped copy is sent to folder ViperDaily. The existing file in ViperDaily is copied to ViperArchive and then deleted from ViperDaily. In other words ViperDaily only ever contains the last backup on any of the computers/external drives.

Forms!CodeBUSystem!Current = ("Viper " + Format(Now, "YYYY-MM-DD") + " " & Format(Now, "hh-mm-ss"))

If Forms!CodeBUSystem!Previous = 0 Then

Forms!CodeBUSystem!Previous = Forms!CodeBUSystem!Current

End If

FileCopy "C:\ViperResides\Viper.mdb", "\\MikeHP\ViperDaily\" & Format([Forms]![CodeBUSystem]![Current]) + ".mdb"

FileCopy "\\MikeHP\ViperDaily\" & Format([Forms]![CodeBUSystem]![Previous]) + ".mdb", "\\MikeHP\ViperArchive\" & Format([Forms]![CodeBUSystem]![Previous]) + ".mdb"

If Forms!CodeBUSystem!Previous <> Forms!CodeBUSystem!Current Then

Kill "\\MikeHP\ViperDaily\" & Format([Forms]![CodeBUSystem]![Previous]) + ".mdb"

End If

Forms!CodeBUSystem!Previous = Forms!CodeBUSystem!Current

I used If Forms!CodeBUSystem!Previous = 0 because I can't get Is Null to work in code and for that reason I still can't quite equal a RunMacro with the macro condtion stopping at [SomeField] Is Not Null. To run down the records with code and stop when the condition field Is Null I used [Somefield] <>1.057534 or whatever. But that is not related to this issue. Thought I would just mention it

 

[namliam]

Why not use "Name", instead of Copy/move??

 

[Mike375]

Why not use "Name", instead of Copy/move??

Name as in

Name "C:\Letters\0Letter1Mike.doc" As "C:\Letters\0Letter1John.doc"

I need to copy/move so as to have all backups in one folder except for the last backup that was run.

 

[namliam]

yes... "name as in"

 

[Mike375]

Some people with lots of different files in a folder love this

CopyFolder "C:\Letters", ("c:\Letters " & Format(Now, "YYYY-MM-DD") + " " & Format(Now, "hh-mm-ss"))

And of course a bat

Start "" "C:\Program Files\Internet Explorer\iexplore.exe" http://www.access-programmers.co.uk/forums/index.php

You can make very good money with small businesses with all this sort of stuff.

 

[Mike375]

What would be good is if this could be run or an alternative so that the clipboard was not cleared after it runs

taskkill /F /IM Winword.EXE

 

[namliam]

Why a bat file to start the explorer, just use "Shell"

Why would you want to kill word???
You can use an object to "FIND" the word instance and close it "nicely"
"GetObject" IIRC

 

[Mike375]

I use this to close Word

WordDoc.Close False
WordObj.Visible = False

Earlier in the code the doc is SaveAs etc

All works well but there can be trouble if there is Scansoft. All that Normal.dot stuff and other things happen. Removing all trusted add ins gets it. But of the person uses OCR then that is a problem.

Taskill does the job but the problem is that the clipboard is cleared and the Word.doc has had a Copy done while open for pasting back into a memo field and also at times to another Word doc that is opened. One the earlier versions of Adobe reader is a problem but in reality it is Ok because most people have updated it.

You can use an object to "FIND" the word instance and close it "nicely"
"GetObject" IIRC

How do you do that?

I am trying to copy a folder from a computer on the network to this one but nothing happens. No error, debug and no copy

CopyFolder "\\MikeHP\ErrorTest", ("c:\ErrorTest")

I thought with the following I could then test if the other computer was turned on

If Len(Dir("c:\ErrorTest", vbDirectory)) > 0 Then

Forms!CodeBUSystem!abc = 1

End If

I am not getting a satisfactory situation with the backup. The On Error Resume Next is great for jumping past but I need to have it put the value in the form/textbox Disk1 done or Computer turned off etc. If I can get avalue because the other computer is turned off then I am there

I have been reading up on this Erro Got T, Resume etc but I am not having success.

 

[namliam]

I use this to close Word

WordDoc.Close False
WordObj.Visible = False

The "visible" has nothing to do with closing anything, it just makes word invisible.

Scansoft. All that Normal.dot stuff and other things happen. Removing all trusted add ins gets it. But of the person uses OCR then that is a problem.

I have no clue on problems related here, might have something to do with the fact you are hiding word... instead of actually quiting it.

You can use an object to "FIND" the word instance and close it "nicely"
"GetObject" IIRC

How do you do that?

Type "GetObject" in code and hit F1.
You use this to find an excisting open version of i.e. Word to take "advantage" of. If there is an open version beeing used allready you can leave the application open... because the user was already using it...

I have been reading up on this Erro Got T, Resume etc but I am not having success.

You can find any error generated by access in the "Err" object, i.e. Err.number
However if you are using an external batch file... the error is in DOS... You could spool the DOS window to a text file and read that... However handling it with FileSearch and copy file by file using FileCopy you can trap the error(s) file by file.