Copying a file from Access 2013

So here is my newest dilemma.

We have a front end / back-end database application in Access 2013.

Since it went live three weeks ago we have been needing to do minor changes here and there to meet the needs of the users.
This has required us to email the users about 2-3 times a day to download the newest update.

There has to be an easier way of updating then relying on the users to update.

So I searched all over and found a couple third party apps that would make this possible but they are expensive.
I did find an article from 2003 that addresses my exact issue.

My front-end has a table, tblClientVersion and a linked table to the back-end, tblServerVersion
When they don't match the app pops up a window asking you to upgrade.  If the user hits yes it closes the front-end, opens up another Database (Update.accdb) and that database does the actual copying of files from the sever to the client.  Then, when complete, it will close itself and open the client backup.

I have no issues with the first part of checking and I have no issues with update.accdb opening up the client.  The issue I am running into is it won't copy the file from the server to the users workstation.

Here is what I am working with:

Option Compare Database
Option Explicit

Dim strSourcePath As String
Dim strDest As String
Dim strPath As String
Dim strBkup As String
Dim strMyDB As String
Dim strVer As String
Dim strSource As String
Dim strMsg As String
Dim strOpenClient As String
Const q As String = """"

Private Sub Form_Open(Cancel As Integer)
' Update status form to identify version being copied.
   strVer = DLookup("[VersionNumber]", "tblVersionServer")
   Me.txtVer.Caption = "Installing version number ... " & strVer

End Sub

Private Sub Form_Timer()
On Error Resume Next

   strMyDB = CurrentDb.Name
   strPath = Left(strMyDB, InStrRev(strMyDB, "\"))
   strDest = Replace(strPath, "c:\Users\%UserName%\Desktop\AIMS", "AIMS.accdb")
   strSourcePath = DLookup("[SourcePath]", "tblSourcePath")
   strSource = strSourcePath & "AIMS.accdb"
   FileCopy strSource, strDest
   strOpenClient = "MSAccess.exe " & q & strDest & "AIMS.accdb" & q
   Shell strOpenClient, vbNormalFocus
End Sub

Open in new window

What am I doing wrong here.?

John SheehySecurity AnalystAsked:
Who is Participating?
Michael PfisterCommented:
Not sure what you're trying to achieve here:
strPath = Left(strMyDB, InStrRev(strMyDB, "\"))
strDest = Replace(strPath, "c:\Users\%UserName%\Desktop\AIMS", "AIMS.accdb")

Open in new window

I assume the AIMS.accdb should be placed on the user desktop in a folder named AIMS.
Get the user desktop by using
Set oWSHShell = CreateObject("WScript.Shell")
strDest = oWSHShell.SpecialFolders("Desktop") & "\AIMS\AIMS.accdb"
Set oWSHShell = Nothing

Open in new window

Gustav BrockCIOCommented:
Use a shortcut and refresh the frontend:

Deploy and update a Microsoft Access application in a Citrix environment

Free to use.
John SheehySecurity AnalystAuthor Commented:
Gustav, that would be a viable solution if we had access to the registry setting on any of our machines.  Company gets  a bit antsy about that.

John SheehySecurity AnalystAuthor Commented:
Michael,  Your solution fixed my current issue and another issue I was having.  Thank you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.