How do I use the constant for the currentdb.name

below is the code I am trying to modify.
I need the const to be the address of the current MDB.

How do I make the const reflect the currentdb.name?

Option Compare Database

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

Function BackupMyDBmyself()
    Dim strBackupFolder As String
    Dim strZipFile As String
    Dim obj As Object
   
   
    Const strYourDB = CurrentDb.Name    'Update this path and file name to be the name of your db
    strBackupFolder = "C:\Athens-lodge-16\Back_up"                       'Location where the backup .zip files will go
 
    If Len(Dir(strBackupFolder, vbDirectory)) = 0 Then MkDir strBackupFolder
    strZipFile = strBackupFolder & "\application" & Format(Now(), "mm-dd-yyyy hh-nn-ss") & ".Zip"         'Update this line to reflect the name you'd like to use for your zip file
    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 4000
        If Len(Dir(strZipFile)) Then Exit Do
    Loop
End Function
Don-WhiteAsked:
Who is Participating?
 
omgangIT ManagerCommented:
If you only need the current db path/name in the specified procedure you don't really need a public variable at all.  You could, instead, have it all in one procedure

Option Compare Database

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

Function BackupMyDBmyself()
    Dim strBackupFolder As String, strYourDB As String
    Dim strZipFile As String
    Dim obj As Object
   
   
    strYourDB = CurrentDb.Name    'Update this path and file name to be the name of your db
    strBackupFolder = "C:\Athens-lodge-16\Back_up"                       'Location where the backup .zip files will go
 
    If Len(Dir(strBackupFolder, vbDirectory)) = 0 Then MkDir strBackupFolder
    strZipFile = strBackupFolder & "\application" & Format(Now(), "mm-dd-yyyy hh-nn-ss") & ".Zip"         'Update this line to reflect the name you'd like to use for your zip file
    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 4000
        If Len(Dir(strZipFile)) Then Exit Do
    Loop
End Function
0
 
omgangIT ManagerCommented:
You can't set the value of a constant at run time.....it's a constant.  Either hard code the value of the constant or change it to a public variable

Public strYourDB = CurrentDb.Name

OM Gang
0
 
Don-WhiteAuthor Commented:
When I use the public as you have it, it gives me an error.

How do I set up the public variable?
0
 
omgangIT ManagerCommented:
Sorry, try this

Option Compare Database

Public strYourDB

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

Function BackupMyDBmyself()
    Dim strBackupFolder As String
    Dim strZipFile As String
    Dim obj As Object
   
   
    strYourDB = CurrentDb.Name    'Update this path and file name to be the name of your db
    strBackupFolder = "C:\Athens-lodge-16\Back_up"                       'Location where the backup .zip files will go
 
    If Len(Dir(strBackupFolder, vbDirectory)) = 0 Then MkDir strBackupFolder
    strZipFile = strBackupFolder & "\application" & Format(Now(), "mm-dd-yyyy hh-nn-ss") & ".Zip"         'Update this line to reflect the name you'd like to use for your zip file
    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 4000
        If Len(Dir(strZipFile)) Then Exit Do
    Loop
End Function
0
 
Don-WhiteAuthor Commented:
Thanks so much.
0
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.

All Courses

From novice to tech pro — start learning today.