Auto copy a copy and open it.

[Juolupuki]

Hi.

I have the database with front end, where some to time needs to be updated and passed to users. Instead asking them manually to copy i found macro which help to automate this process.
The issue i having is that macro creates a loop, it tries to open multiple front ends unless the "Security Warning" trusted (please see attached for warning).
It works fine if it's trusted and next time no issue, but still some users forgets to do it or not understand much about it.

Please see code I'm using:

Private Sub Form_Timer()
On Error Resume Next

    Dim strSource As String
    Dim strMsg As String
    Dim strOpenClient As String
    Const q As String = """"

    DoCmd.Hourglass True
    DoEvents
    
    Err.Clear
    
    strSource = "W:\Database\Database.accdb"
    strDest = "C:\Database\Database.accdb"
    
    FileCopy strSource, strDest
    
    DoEvents
    
    strOpenClient = "MSAccess.exe " & q & strDest & q
    Shell strOpenClient, vbNormalFocus

    DoCmd.Hourglass False
    DoCmd.Quit

End Sub

What did I find out that it opens first access application/client after the actual file and in this case it can be open as many time you want. Tried to find the solution to open just from the directory, unlucky with out results

Is there actual way to avoid the macro trust messages? As much i know it can be done manually in application, but that isn't solution we have over 50 PCs and they constantly changing to new ones.

Thanks in advance, for any help!

 

[CJ_London]

Is there actual way to avoid the macro trust messages?

each user needs to create a trusted directory and put the copy there. This directory path is stored in their registry - so can only be done if the user has the appropriate permissions. I've yet to find it as an issue across many clients.

There is code you can use to do this - but still needs to be run once. See this link for an example https://www.tek-tips.com/viewthread.cfm?qid=1718392

Also recommend you do not use mapped drives, use the UNC path instead which will be the same for everyone. And better you use a folder in the user profile which is more consistent e.g. 'C:\Users\{username}\AppData\Local\Database\Database.accdb'

Finally give your folders and files a meaningful name

 

[Solo712]

Hi.

I have the database with front end, where some to time needs to be updated and passed to users. Instead asking them manually to copy i found macro which help to automate this process.
The issue i having is that macro creates a loop, it tries to open multiple front ends unless the "Security Warning" trusted (please see attached for warning).
It works fine if it's trusted and next time no issue, but still some users forgets to do it or not understand much about it.

Please see code I'm using:

Private Sub Form_Timer()
On Error Resume Next

    Dim strSource As String
    Dim strMsg As String
    Dim strOpenClient As String
    Const q As String = """"

    DoCmd.Hourglass True
    DoEvents
    
    Err.Clear
    
    strSource = "W:\Database\Database.accdb"
    strDest = "C:\Database\Database.accdb"
    
    FileCopy strSource, strDest
    
    DoEvents
    
    strOpenClient = "MSAccess.exe " & q & strDest & q
    Shell strOpenClient, vbNormalFocus

    DoCmd.Hourglass False
    DoCmd.Quit

End Sub

What did I find out that it opens first access application/client after the actual file and in this case it can be open as many time you want. Tried to find the solution to open just from the directory, unlucky with out results

Is there actual way to avoid the macro trust messages? As much i know it can be done manually in application, but that isn't solution we have over 50 PCs and they constantly changing to new ones.

Thanks in advance, for any help!

You need to make sure that the access to the Timer event is restricted to copy the new front end only once. The best way to handle this is to have a small update table on both the Front- and Back ends. The users' front end would check if they are updated to the last available version (by version , release number, or date stamp on the back end) and if not, copy the new version identifier(s) to the local table and run the timer event. If the version, release or date are equal in the two tables the Timer event would be cancelled (Me.TimerInterval = 0) irrespective of the issue with the Trusted Location - which is separate and easily handled as CJ points out.

Best,
Jiri