DJRhino1175
(...) DB I needed to copy because I got an Access denied error. (...)
I suspect that you are attempting to copy the database while it is open/loaded, of course, this will not work.
In years past, I had a batch file (yes the old MSDOS days) that I had bodged together that the users would run via a shortcut on the desktop. The batch file would copy the front-end to the local drive every time. Worked like a charm for a very long time.
Looking around one day I ran across a variant of this which runs from within Access - YEA! It's a lot more flexible and works without much effort on my end
So what I have now is a table with versioning numbers in both the front and backend
In the front end:
[t_FeMetadata]
[pk_v][BE_V][FE_V]
In the back end
[t_BeMetadata]
[pk_v][BE_V][FE_V][FE_MstrLctn]
I have the following code in a Macro - [AutoExec]
(I've removed some of the form calling script - it has sensitive information - should be very easy to add in at the user's end.)
This macro checks to see if the database is in a trusted state and if so it launches the splash/re-linking form:
- <?xml version="1.0" encoding="UTF-16" standalone="no"?>
-
<UserInterfaceMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
-
<UserInterfaceMacro MinimumClientDesignVersion="14.0.0000.0000">
-
<Statements/>
-
<Sub Name="autostart">
-
<Statements>
-
<ConditionalBlock>
-
<If>
-
<Condition>[currentproject].[istrusted]</Condition>
-
<Statements>
-
<Action Name="MessageBox">
-
<Argument Name="Message">You would place your action code to open the re-linking form here so that the form's onload code will execute</Argument>
-
<Argument Name="Type">Information</Argument>
-
<Argument Name="Title">VBA Code Should Run, DB in Trusted Status.</Argument>
-
</Action>
-
</Statements>
-
</If>
-
<Else>
-
<Statements>
-
<Action Name="MessageBox">
-
<Argument Name="Message">The database is currently not in a trusted state.@Many of the features of the database are currently disabled.@Please allow content and add to your trusted documents list. You may need to close and reopen the file after adding to the trusted documents.</Argument>
-
<Argument Name="Type">Critical</Argument>
-
<Argument Name="Title">Database Is Partialy Disabled. Please Enable and Add to Trusted Documents.</Argument>
-
</Action>
-
</Statements>
-
</Else>
-
</ConditionalBlock>
-
</Statements>
-
</Sub>
-
</UserInterfaceMacro>
-
</UserInterfaceMacros>
Once the relinking/splash form loads the on_load event triggers:
+ checks to make sure that the currently opened front-end isn't the Master Copy. If it is then it prompts the user for location on their PC and verifies that they can read/write to that document (this is then passed to the following vba module instead of the current location to copy the master to and the remaining checks are skipped).
+ checks to see if the backend is in the same location and if so then it links the [t_BeMetadata] first, checks the versioning numbers to make sure that the front and back ends are compatible, if front end version number is different in the backend then it passes the current path for the database that is loaded the location of the master-file
((side note: during the final beta stages I'll often just skip the version checks and just have the current Master FE file copied over the user's version regardless of the situation))
+ starts the following code:
(I don't remember where I found the concept now; however, I've seen versions of this in a lot of VBA forums. If the author is reading this - THANK YOU for your help!)
- Option Compare Database
-
Option Explicit
-
' global variable for path to original database location
-
Public g_strFilePath As String
-
' global variable for path to database to copy from
-
Public g_strCopyLocation As String
-
-
-
Public Sub UpdateFrontEnd()
-
Dim strCmdBatch As String
-
Dim notNotebook As Object
-
Dim FSys As Object
-
Dim TestFile As String
-
Dim strKillFile As String
-
Dim strReplFile As String
-
Dim strRestart As String
-
-
' sets the file name and location for the file to delete
-
strKillFile = g_strFilePath
-
' sets the file name and location for the file to copy
-
strReplFile = g_strCopyLocation & "\" & CurrentProject.Name
-
' sets the file name of the batch file to create
-
TestFile = CurrentProject.Path & "\UpdateDbFE.cmd"
-
' sets the restart file name
-
strRestart = """" & strKillFile & """"
-
' creates the batch file
-
Open TestFile For Output As #1
-
Print #1, "Echo Off"
-
Print #1, "ECHO Deleting old file"
-
Print #1, ""
-
Print #1, "ping 1.1.1.1 -n 1 -w 2000"
-
Print #1, ""
-
Print #1, "Del """ & strKillFile & """"
-
Print #1, ""
-
Print #1, "ECHO Copying new file"
-
Print #1, "Copy /Y """ & strReplFile & """ """ & strKillFile & """"
-
Print #1, ""
-
Print #1, "CLICK ANY KEY TO RESTART THE ACCESS PROGRAM"
-
Print #1, "START /I " & """MSAccess.exe"" " & strRestart
-
Close #1
-
'Exit Sub
-
' runs the batch file
-
Shell TestFile
-
-
'closes the current version and runs the batch file
-
DoCmd.Quit
-
-
End Sub
-
This basically re-creates the same batch file I used to do by hand, shells the command outside of Access, closes the current Access session, copies the new Access over, and restarts a new Access session.
BEAUTY OF THIS METHOD:
It runs in every version of the front-end file.
You do NOT need to loop through tables of user names, make sure your profile as read/write permission to the user's directory etc...
DRAWBACK: User MUST have at least Read permission to the Master Front-End location. My normal arrangement
<MainFolder>Front_end.Accdb
<BeDataFolder>Back_End.ACCDB</BeDataFolder>
</MainFolder>