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