Consulting

Results 1 to 13 of 13

Thread: Copy DB to multiple drives

  1. #1
    VBAX Newbie
    Joined
    Jun 2017
    Posts
    5
    Location

    Unhappy Copy DB to multiple drives

    hi, I am needing assistance. The below code copies the DB to the C:\ and P:\ drive, however when opening the copied DB it runs through the "You are opening up the database from it's root directory..." on both drives and of course the DB doesn't open for use. It appears to not recognize that it is located on the c:\ or p:\ drive, I am not sure what I have wrong with my coding. When I remove the "Or Left(getdbpath, 3) <>"p:\" it works fine. but the OR seems to make it not work properly.

    Can someone help me out, please?

    If Left(GetDBPath, 3) <> "c:\" Or Left(GetDBPath, 3) <> "p:\" Then    MsgBox ("You are opening up the database from it's root directory, a copy of the database will be copied to your desktop. Click OK below to begin copying.")
    
    
        On Error Resume Next
        Kill "C:\Users\Public\Desktop\CM MAT 2.0 USERCOPY.accdb"
        Kill "C:\Users\Public\Desktop\Backup of CM MAT 2.0 USERCOPY.accdb"
        Kill "C:\Users\Public\Desktop\Backup of Backup of CM MAT 2.0 USERCOPY.accdb"
        Kill "C:\Users\Public\Desktop\Backup of Backup of Backup of CM MAT 2.0 USERCOPY.accdb"
        oldPath = "\\vcocfscl01\okcshare\Operations Management\DEPART TOOLS\MAT\Collateral Management\" 'Folder file is located in
        UserLogIn = GetUserName
        newPath = "C:\Users\" & UserLogIn & "\Desktop\" 'Folder to copy file to
        Set fs = CreateObject("Scripting.FileSystemObject")
        fs.CopyFile oldPath & "CM MAT 2.0 USERCOPY.accdb", newPath & "CM MAT 2.0 USERCOPY.accdb"
        
        Kill "P:\CM MAT 2.0 USERCOPY.accdb"
        Kill "P:\Backup of CM MAT 2.0 USERCOPY.accdb"
        Kill "P:\Backup of Backup of CM MAT 2.0 USERCOPY.accdb"
        Kill "P:\Backup of Backup of Backup of CM MAT 2.0 USERCOPY.accdb"
    
    
        oldPath = "\\vcocfscl01\okcshare\Operations Management\DEPART TOOLS\MAT\Collateral Management\" 'Folder file is located in
        UserLogIn = GetUserName
        newPathp = "P:\"
        Set fs = CreateObject("Scripting.FileSystemObject")
        fs.CopyFile oldPath & "CM MAT 2.0 USERCOPY.accdb", newPathp & "CM MAT 2.0 USERCOPY.accdb"
    
    
        Set fs = Nothing
        DoCmd.Close acForm, "CK_DB_Version", acSaveYes
       
       MsgBox ("Database is copied to your desktop, database will now be closed.  Please re-open db from your desktop.")
       Application.Quit

  2. #2
    VBAX Expert
    Joined
    Oct 2012
    Posts
    726
    Location
    If there are only 2 drives, c and p, Left(GetDBPath, 3) will always be c:\ or p:\


    Why does it matter where they run the file from? Seems like this is what shortcuts are for.

  3. #3
    Quote Originally Posted by jonh View Post
    If there are only 2 drives, c and p, Left(GetDBPath, 3) will always be c:\ or p:\


    Why does it matter where they run the file from? Seems like this is what shortcuts are for.
    It matters a lot for multiple conncurrent user databases.

    I do the same type of check to follow the Best Practice of having the front end on a local and NOT shared.

    This idea is to be sure the user is not opening the "master" copy on the server. You want them to open their own copy of the front end that is not shared.
    Boyd Trimmell aka HiTechCoach
    Microsoft Access MVP -2010-2015

    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  4. #4
    Quote Originally Posted by 851jme View Post
    hi, I am needing assistance. The below code copies the DB to the C:\ and P:\ drive, however when opening the copied DB it runs through the "You are opening up the database from it's root directory..." on both drives and of course the DB doesn't open for use. It appears to not recognize that it is located on the c:\ or p:\ drive, I am not sure what I have wrong with my coding. When I remove the "Or Left(getdbpath, 3) <>"p:\" it works fine. but the OR seems to make it not work properly.

    Can someone help me out, please?

    If Left(GetDBPath, 3) <> "c:\" Or Left(GetDBPath, 3) <> "p:\" Then    MsgBox ("You are opening up the database from it's root directory, a copy of the database will be copied to your desktop. Click OK below to begin copying.")
    
    
        On Error Resume Next
        Kill "C:\Users\Public\Desktop\CM MAT 2.0 USERCOPY.accdb"
        Kill "C:\Users\Public\Desktop\Backup of CM MAT 2.0 USERCOPY.accdb"
        Kill "C:\Users\Public\Desktop\Backup of Backup of CM MAT 2.0 USERCOPY.accdb"
        Kill "C:\Users\Public\Desktop\Backup of Backup of Backup of CM MAT 2.0 USERCOPY.accdb"
        oldPath = "\\vcocfscl01\okcshare\Operations Management\DEPART TOOLS\MAT\Collateral Management\" 'Folder file is located in
        UserLogIn = GetUserName
        newPath = "C:\Users\" & UserLogIn & "\Desktop\" 'Folder to copy file to
        Set fs = CreateObject("Scripting.FileSystemObject")
        fs.CopyFile oldPath & "CM MAT 2.0 USERCOPY.accdb", newPath & "CM MAT 2.0 USERCOPY.accdb"
        
        Kill "P:\CM MAT 2.0 USERCOPY.accdb"
        Kill "P:\Backup of CM MAT 2.0 USERCOPY.accdb"
        Kill "P:\Backup of Backup of CM MAT 2.0 USERCOPY.accdb"
        Kill "P:\Backup of Backup of Backup of CM MAT 2.0 USERCOPY.accdb"
    
    
        oldPath = "\\vcocfscl01\okcshare\Operations Management\DEPART TOOLS\MAT\Collateral Management\" 'Folder file is located in
        UserLogIn = GetUserName
        newPathp = "P:\"
        Set fs = CreateObject("Scripting.FileSystemObject")
        fs.CopyFile oldPath & "CM MAT 2.0 USERCOPY.accdb", newPathp & "CM MAT 2.0 USERCOPY.accdb"
    
    
        Set fs = Nothing
        DoCmd.Close acForm, "CK_DB_Version", acSaveYes
       
       MsgBox ("Database is copied to your desktop, database will now be closed.  Please re-open db from your desktop.")
       Application.Quit

    Are you sure the one you are opening the local copy on the desktop and NOT a shortcut?

    As a test, open the database, go to the immediate window (Ctrl-G) and type the following:

    ? GetDBPath
    or

    Print GetDBPath
    then press the enter key.

    This sill show you where it thinks the database is located.
    Boyd Trimmell aka HiTechCoach
    Microsoft Access MVP -2010-2015

    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    VBAX Newbie
    Joined
    Jun 2017
    Posts
    5
    Location
    I checked through the immediate window on all copies of the database (s, c & p drives), the are all returning the location they are at.

  6. #6
    VBAX Expert
    Joined
    Oct 2012
    Posts
    726
    Location
    Quote Originally Posted by HiTechCoach View Post
    It matters a lot for multiple conncurrent user databases.

    I do the same type of check to follow the Best Practice of having the front end on a local and NOT shared.

    This idea is to be sure the user is not opening the "master" copy on the server. You want them to open their own copy of the front end that is not shared.
    No it doesn't.

    What matters is that the front end is split properly from the back end and users aren't sharing temp tables. If you run a split front end off a server you are still running a local 'copy'.

  7. #7
    VBAX Newbie
    Joined
    Jun 2017
    Posts
    5
    Location
    hi, I split the database through the access wizard, is there a different way?

    it doesn't appear to recognized the drive it is on once the coped database is opened. The Or statement is missing something I guess and I can't figure it out. It works if I remove the Or and everything after it, 1 drive at a time.

  8. #8
    VBAX Expert
    Joined
    Oct 2012
    Posts
    726
    Location
    Split will be fine.

    If CreateObject("Scripting.FileSystemObject").GetFolder(CurrentProject.Path).Type = "Local Disk" Then
        MsgBox ("You are opening up the database from it's root directory, " & _
            "a copy of the database will be copied to your desktop. Click OK below to begin copying.")
        Exit Sub
    End If

  9. #9
    VBAX Newbie
    Joined
    Jun 2017
    Posts
    5
    Location
    thank you Jonh, I have included the code again to show where I placed it (not sure if this is correct place). It didn't work :-(

    Sub CopyDB()Dim fs As Object
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim oldPath As String, newPath As String
    Dim newpathp As String
    Dim sDest As String
    Dim sSource As String
    Set db = CurrentDb
    
    
    
    
    
    
    SQL = "Select * from _DBVersionLocal"
    
    
    Set rs = db.OpenRecordset(SQL)
         
       'Retrieve value if data is found
       If rs.EOF = False Then
          LocalVersion = rs("VersionNumber")
       Else
          rs.Close
          Set rs = Nothing
       End If
    ''''
    
    
    Set db = CurrentDb
    
    
    SQL = "Select * from _DBVersionMaster"
    
    
    Set rs = db.OpenRecordset(SQL)
         
       'Retrieve value if data is found
       If rs.EOF = False Then
          MasterVersion = rs("VersionNumber")
       Else
          rs.Close
          Set rs = Nothing
       End If
       
    'test code
    If CreateObject("Scripting.FileSystemObject").GetFolder(CurrentProject.Path).Type = "Local Disk" Then
        MsgBox ("Are opening up the database from it's root directory, " & _
        "a copy of the database will be copied to your desktop. Click OK below to begin copying.")
        Exit Sub
        
    End If
    If LocalVersion <> MasterVersion Then
        MsgBox ("There has been an update to the database that requires the database to be copied to your desktop.  Click OK below to begin copying.")
        
        On Error Resume Next
        Kill "C:\Users\[GetUserName]\Desktop\CM MAT 2.0 USERCOPY.accdb"
        Kill "C:\Users\Public\Desktop\Backup of CM MAT 2.0 USERCOPY.accdb"
        Kill "C:\Users\Public\Desktop\Backup of Backup of CM MAT 2.0 USERCOPY.accdb"
        Kill "C:\Users\Public\Desktop\Backup of Backup of Backup of CM MAT 2.0 USERCOPY.accdb"
        Kill "P:\CM MAT 2.0 USERCOPY.accdb"
        Kill "P:\Backup of CM MAT 2.0 USERCOPY.accdb"
        Kill "P:\Backup of Backup of CM MAT 2.0 USERCOPY.accdb"
        Kill "P:\Backup of Backup of Backup of CM MAT 2.0 USERCOPY.accdb"
    
    
        oldPath = "\\vcocfscl01\okcshare\Operations Management\DEPART TOOLS\MAT\Collateral Management\" 'Folder file is located in
        UserLogIn = GetUserName
        newPath = "C:\Users\" & UserLogIn & "\Desktop\" 'Folder to copy file to
            newpathp = "P:\"
        Set fs = CreateObject("Scripting.FileSystemObject")
        fs.CopyFile oldPath & "CM MAT 2.0 USERCOPY.accdb", newPath & "CM MAT 2.0 USERCOPY.accdb"
            fs.CopyFile oldPath & "CM MAT 2.0 USERCOPY.accdb", newpathp & "CM MAT 2.0 USERCOPY.accdb"
      
        Set fs = Nothing
        DoCmd.Close acForm, "CK_DB_Version", acSaveYes
    End If
    
    
    If Left(GetDBPath, 3) <> "C:\" Or Left(GetDBPath, 3) <> "P:\" Then
        MsgBox ("You are opening up the database from it's root directory, a copy of the database will be copied to your desktop. Click OK below to begin copying.")
        
        On Error Resume Next
        Kill "C:\Users\Public\Desktop\Desktop\CM MAT 2.0 USERCOPY.accdb"
        Kill "C:\Users\Public\Desktop\Backup of CM MAT 2.0 USERCOPY.accdb"
        Kill "C:\Users\Public\Desktop\Backup of Backup of CM MAT 2.0 USERCOPY.accdb"
        Kill "C:\Users\Public\Desktop\Backup of Backup of Backup of CM MAT 2.0 USERCOPY.accdb"
        Kill "P:\CM MAT 2.0 USERCOPY.accdb"
        Kill "P:\Backup of CM MAT 2.0 USERCOPY.accdb"
        Kill "P:\Backup of Backup of CM MAT 2.0 USERCOPY.accdb"
        Kill "P:\Backup of Backup of Backup of CM MAT 2.0 USERCOPY.accdb"
    
    
        oldPath = "\\vcocfscl01\okcshare\Operations Management\DEPART TOOLS\MAT\Collateral Management\" 'Folder file is located in
        UserLogIn = GetUserName
        newPath = "C:\Users\" & UserLogIn & "\Desktop\" 'Folder to copy file to
            newpathp = "P:\"
        Set fs = CreateObject("Scripting.FileSystemObject")
        fs.CopyFile oldPath & "CM MAT 2.0 USERCOPY.accdb", newPath & "CM MAT 2.0 USERCOPY.accdb"
            fs.CopyFile oldPath & "CM MAT 2.0 USERCOPY.accdb", newpathp & "CM MAT 2.0 USERCOPY.accdb"
      
        Set fs = Nothing
        DoCmd.Close acForm, "CK_DB_Version", acSaveYes
       
       MsgBox ("Database is copied to your desktop, database will now be closed.  Please re-open db from your desktop.")
       Application.Quit
       End If
    
    
    End Sub

  10. #10
    VBAX Expert
    Joined
    Oct 2012
    Posts
    726
    Location
    Quote Originally Posted by 851jme View Post
    thank you Jonh, I have included the code again to show where I placed it (not sure if this is correct place). It didn't work :-(
    What didn't work?
    Add a breakpoint and step through your code.

  11. #11
    VBAX Newbie
    Joined
    Jun 2017
    Posts
    5
    Location
    Thank you both for your time, I've decided they will just have to re download it when they are using it from the P:\ drive.

  12. #12
    Quote Originally Posted by jonh View Post
    If you run a split front end off a server you are still running a local 'copy'.
    That funny .
    Boyd Trimmell aka HiTechCoach
    Microsoft Access MVP -2010-2015

    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  13. #13
    VBAX Expert
    Joined
    Oct 2012
    Posts
    726
    Location
    Quote Originally Posted by HiTechCoach View Post
    That funny .
    Wasn't a joke.

    Access databases are copied from servers, that's why they aren't as efficient as proper database engines.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •