Back Up Access Database From Access

I'm trying to use the following code and launch it w/a macro, but I'm getting errors on the function. Here's what I've got,

Function BackupMyDBmyself()
    Dim strBackupFolder As String, strYourDB As String
    Dim strZipFile As String
    Dim obj As Object
   
    Const strYourDB As String = "Z:\@MasterDatabase\CCOLearningHub2007.mdb"
    strBackupFolder = "Z:\@MasterDatabase\@dbase_bk"
 
    If Len(Dir(strBackupFolder, vbDirectory)) = 0 Then MkDir strBackupFolder
    strZipFile = strBackupFolder & Format("yyyy-mm-dd hh:mm:ss") & "\CCOLearningHub" & ".Zip"
    CreateObject("Scripting.FileSystemObject").CreateTextFile(strZipFile).Write CStr(Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0))
    Set obj = CreateObject("Shell.Application")
    obj.NameSpace(CStr(strZipFile)).CopyHere strYourDB
    Do
        Sleep 3000
        If Len(Dir(strZipFile)) Then Exit Do
    Loop
End Function

ERROR:

"Compile error:
Duplicate declaration in current scope."

 . . . with bolded section highlighted.
eossmaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

eossmaAuthor Commented:
Thoughts on how I could get this to work within access?
0
Wayne Taylor (webtubbs)Commented:
You have "strYourDB" declared twice. If it's a constant, remove it from the first line of the routine.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Also:

Constants generally should not be declared in a Function, but rather in the General Declarations section of a Module. Constants are typically values which will not change for the entire program, and should be scoped at a global level.

I'd suggest you do this:

Dim strBackupFolder As String, strYourDB As String
Dim strZipFile As String
Dim obj As Object
   
strYourDB = "Z:\@MasterDatabase\CCOLearningHub2007.mdb"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Gustav BrockCIOCommented:
> Constants generally should not be declared in a Function ...

That certainly depends. If a constant is for the internal use of this function alone, it should be declared in the function.

However, the constant in question is not a feature of the function (it should be supposed to work for any valid path/file) rather than of the application, thus is should be declared elsewhere ... if at all, because I would regard a file destination as a parameter adjustable by a (super)user.

So I vote for your solution: Remove the "Const".

/gustav
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
That certainly depends. If a constant is for the internal use of this function alone, it should be declared in the function.
Hence the reason I included the word "generally".

I realize that, as far as Access and VBA are concerned, the only real difference between a Constant and a Variable is the fact that you can define a Variable at runtime, so there's really no difference in the two for the situation described in this question (I understand there's some under-the-covers differences regarding compilation and such, but those aren't really relevant to the usage here).

But from a general programming point of view - and this is purely my own interpretation, of course - it would seem that a Constant is better used at Application level, rather than at function/sub level.
0
PatHartmanCommented:
You didn't say what the error message was so debugging it for you is a little more challenging.  Here is one error-

strBackupFolder = "Z:\@MasterDatabase\@dbase_bk"
Should be
strBackupFolder = "Z:\@MasterDatabase\@dbase_bk\"

Here is another-
strZipFile = strBackupFolder & Format("yyyy-mm-dd hh:mm:ss") & "\CCOLearningHub" & ".Zip"
Should be
strZipFile = strBackupFolder & Format(Now(), "yyyy-mm-dd hh:mm:ss") & "\CCOLearningHub" & ".Zip"

In the first case, you need to add the backslash to the end of the string when you create it or each time you use it.  In the second, you were missing the variable you wanted to format.

When getting errors, ALWAYS use debug to view your variables.  You would have seen the mal formed file name.
0
eossmaAuthor Commented:
The error message is at the bottom of my note, with the highlighted as the point of error.
0
Gustav BrockCIOCommented:
And it is pretty clear. You cannot:

    Dim strYourDB As String
    Const strYourDB As String = "Z:\@MasterDatabase\CCOLearningHub2007.mdb"

As Wayne and Scott explained, it either has to be:

    Dim strYourDB As String
    strYourDB = "Z:\@MasterDatabase\CCOLearningHub2007.mdb"

or:

    Const strYourDB As String = "Z:\@MasterDatabase\CCOLearningHub2007.mdb"

/gustav
0
eossmaAuthor Commented:
Based on input, I changed the script to, but I get an error on the bolded below. Error code is at bottom.

Function BackupMyDBmyself()
    Dim strBackupFolder As String, strYourDB As String
    Dim strZipFile As String
    Dim obj As Object
   
    strYourDB = "Z:\@MasterDatabase\CCOLearningHub2007.mdb"
    strBackupFolder = "Z:\@MasterDatabase\@dbase_bk\"
 
    If Len(Dir(strBackupFolder, vbDirectory)) = 0 Then MkDir strBackupFolder
    strZipFile = strBackupFolder & Format(Now(), "yyyy-mm-dd hh:mm:ss") & "\CCOLearningHub" & ".Zip"
   CreateObject("Scripting.FileSystemObject").CreateTextFile(strZipFile).Write CStr(Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0))
    Set obj = CreateObject("Shell.Application")
    obj.NameSpace(CStr(strZipFile)).CopyHere strYourDB
    Do
        Sleep 3000
        If Len(Dir(strZipFile)) Then Exit Do
    Loop
End Function

_________________________________________

Run-time error '52':
Bad file name or number
0
Gustav BrockCIOCommented:
That's because you here:

Format(Now(), "yyyy-mm-dd hh:mm:ss")

insert colons in the filename. That's a no-no.

/gustav
0
eossmaAuthor Commented:
<sigh>
ok, fixed that, but now it gives me:

Run-time error '76':
Path not found
0
eossmaAuthor Commented:
on the same line:

CreateObject("Scripting.FileSystemObject").CreateTextFile(strZipFile).Write CStr(Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0))
0
Gustav BrockCIOCommented:
Well, then you must either create the path or adjust your expression to return an existing path.

    Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)

seems to return what looks like a header of a zip file. It is not very normal to write zip files byte-wise. In fact, I've never seen it done before as it is a major task well above the level of your other questions. You would use a library for this.

/gustav
0
eossmaAuthor Commented:
Yeah I was looking at these characters and it seems excessive.

Could you hep me adjust the expression to return the existing path?
0
Gustav BrockCIOCommented:
Yes, if you tell me/us what it is.

/gustav
0
eossmaAuthor Commented:
I'm missing something here. I'm using code I found on EE and am trying to adjust it to my purposes.

I want
to copy my db from = "Z:\@MasterDatabase\CCOLearningHub2007.mdb"
to = "Z:\@MasterDatabase\@dbase_bk\"
in this format= "(Now(), "yyyy-mm-dd hh:mm:ss") & "\CCOLearningHub" & ".Zip"

How could the code be tweaked to accomplish that?
0
Gustav BrockCIOCommented:
Like this:
Public Function ZipCurrentProject() As Long

    Dim ShellApplication    As Object
    
    Dim CurrentProjectFile  As String
    Dim ZipPath             As String
    Dim ZipName             As String
    Dim ZipFile             As String
    Dim FileNumber          As Integer
    
    ' File and folder names.
    CurrentProjectFile = CurrentProject.Path & "\" & CurrentProject.Name
    ' The path must exist.
    ZipPath = CurrentProject.Path & "\@dbase_bk" & Format(Now, " yyyy-mm-dd hh.nn.ss") & "\"
    ZipName = "CCOLearningHub.zip"
    ZipFile = ZipPath & ZipName
    
    ' Create sub folder if missing.
    If Dir(ZipPath, vbDirectory) = "" Then
        MkDir ZipPath
    End If
    
    ' Create empty zip folder.
    FileNumber = FreeFile
    Open ZipFile For Output As #FileNumber
    Print #FileNumber, Chr(80) & Chr(75) & Chr(5) & Chr(6) & String(18, vbNullChar)
    Close #FileNumber
    
    Set ShellApplication = CreateObject("Shell.Application")
    ' Copy the project file into the zip file.
    With ShellApplication
        Debug.Print Timer, "zipping started ..."
        .Namespace(CVar(ZipFile)).CopyHere CVar(CurrentProjectFile)
        ' Ignore error while looking up the zipped file before is has been added.
        On Error Resume Next
        ' Wait for the file to created.
        Do Until .Namespace(CVar(ZipFile)).Items.Count = 1
            ' Wait a little ...
            'DoEvents
            Sleep 100
            Debug.Print " .";
        Loop
        Debug.Print
        ' Resume normal error handling.
        On Error GoTo 0
        Debug.Print Timer, "zipping finished."
    End With
    
    Set ShellApplication = Nothing
    
    ZipCurrentProject = Err.Number

End Function

Open in new window

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
eossmaAuthor Commented:
Really appreciate you doing this!

After running it, I get an error on line 40:

Compile Error:
Sub or Function not defined.
0
Gustav BrockCIOCommented:
0
eossmaAuthor Commented:
So, to wrap this up, here's what worked for me.  Thank you.

____________________________________________________________________

Option Compare Database
Option Explicit

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public Function BackupMyDBmyself_Click() As Long

    Dim ShellApplication    As Object
    Dim CurrentProjectFile  As String
    Dim ZipPath             As String
    Dim ZipName             As String
    Dim ZipFile             As String
    Dim FileNumber          As Integer
   
    ' File and folder names.
    CurrentProjectFile = CurrentProject.Path & "\" & CurrentProject.Name
    ' The path must exist.
    ZipPath = CurrentProject.Path & "\@dbase_bk\" & Format(Now, " yyyy-mm-dd_hh.nn.ss") & "\"
    ZipName = "CCOLearningHub.zip"
    ZipFile = ZipPath & ZipName
   
    ' Create sub folder if missing.
    If Dir(ZipPath, vbDirectory) = "" Then
        MkDir ZipPath
    End If
   
    ' Create empty zip folder.
    FileNumber = FreeFile
    Open ZipFile For Output As #FileNumber
    Print #FileNumber, Chr(80) & Chr(75) & Chr(5) & Chr(6) & String(18, vbNullChar)
    Close #FileNumber
   
    Set ShellApplication = CreateObject("Shell.Application")
    ' Copy the project file into the zip file.
    With ShellApplication
        Debug.Print Timer, "zipping started ..."
        .NameSpace(CVar(ZipFile)).CopyHere CVar(CurrentProjectFile)
        ' Ignore error while looking up the zipped file before is has been added.
        On Error Resume Next
        ' Wait for the file to created.
        Do Until .NameSpace(CVar(ZipFile)).Items.Count = 1
            ' Wait a little ...
            'DoEvents
            Sleep 50
            Debug.Print " .";
        Loop
        Debug.Print
        ' Resume normal error handling.
        On Error GoTo 0
        Debug.Print Timer, "zipping finished."
    End With
   
    Set ShellApplication = Nothing
   
    'ZipCurrentProject = Err.Number

    'DoCmd.Quit
   
End Function
0
Gustav BrockCIOCommented:
Grade B?

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.