Copy DB to multiple drives

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

Code:

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

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.

Quote:

Originally Posted by 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.

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.

Quote:

Originally Posted by 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?

Code:

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:

Code:

? GetDBPath

or

Code:

Print GetDBPath

then press the enter key.

This sill show you where it thinks the database is located.

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.

Quote:

Originally Posted by HiTechCoach

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

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.

Split will be fine.

Code:

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

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

Code:

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

Quote:

Originally Posted by 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 :-(

What didn't work?
Add a breakpoint and step through your code.

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.

Quote:

Originally Posted by jonh

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

That funny .

Quote:

Originally Posted by HiTechCoach

That funny .

Wasn't a joke.

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