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 SheehySystem Security ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
Use a shortcut and refresh the frontend:

Deploy and update a Microsoft Access application in a Citrix environment

Free to use.
John SheehySystem Security ManagerAuthor 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 SheehySystem Security ManagerAuthor Commented:
Michael,  Your solution fixed my current issue and another issue I was having.  Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.