Copy DB to multiple drives

[851jme]

:crying: 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

[jonh]

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.

[HiTechCoach]

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.

[HiTechCoach]

:crying: 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.

[851jme]

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.

[jonh]

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'.

[851jme]

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.

[jonh]

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

[851jme]

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

[jonh]

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.

[851jme]

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.

[HiTechCoach]

If you run a split front end off a server you are still running a local 'copy'.

That funny .

[jonh]

That funny .

Wasn't a joke.

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