Auto Front End Updater

[Patrick Brydone]

I am running an Access 97 database with the backend on the server and client databases on about 2 workstations. I also have a working model of the frontend on the server that I often update. I then have to go around to each workstation and manually install the new front end from the server. I am looking for an application built in Access 97 that I can apply before the client database opens, it checks the client on the server and if a newer version is available, it copies it to the workstation. Once copied, it then proceeds to open the new front end on the workstation. Tony Towes has an excellent application on the Granite website but I cant use it because it contains a VB programme that I cannot import onto our network (security restrictions).

Grateful if someone could point me in the right direction.

 

[Treason]

Well I use a similiar setup except I use a vb app to check the version and then copy the new frontend and the launch the frontend. But if you cannot use an exe.. then you would probaly have to create an access database that can download the new version, then launch the real access frontend. The only part that might be tricky, only because I have not tried yet, is to close the current database and open a new one.

 

[ByteMyzer]

You can actually program your front-end to copy itself from the server.

Paste the following code into a new module in your front-end database file:

Private Const fePath As String = "C:\Local Folder\MyDbFrontEnd.mdb"
' Change the above constant to your intended front-end file path
' on the local computer

Private Function KillFile(fPath As String) As Boolean
On Error GoTo KillFile_Error
    If Dir(fPath) > "" Then Kill fPath
    KillFile = True
Exit Function
KillFile_Error:
    KillFile = False
End Function

Public Function AutoExecCopy()
    ' If the current instance of the database is not being run 
    ' from the local drive, delete the local copy and copy the
    ' front-end file stored on the server;
    If Left(CurrentDb.Name, 3) <> "C:\" Then
        If KillFile(fePath) = True Then
            FileCopy CurrentDb.Name, fePath
        End If
        Shell """" & SysCmd(acSysCmdAccessDir) _
            & "msaccess.exe"" " & """" _
            & fePath & """", vbMaximizedFocus
        Application.Quit
    ' otherwise, proceed to your application's startup routine.
    ' In this case, to open the form named "frmMain"
    Else
        DoCmd.OpenForm "frmMain"
    End If
End Function

Then have your Autoexec macro perform RunCode AutoExecCopy()

Finally, set the local computer shortcut to point to the server path of the front-end database file. Ex: \\MyServer\Database\MyDbFrontEnd.mdb

This will run the front-end from the server. The database sees that it is not being run locally, so it copies itself into the local computer's folder and executes a shell command to run the local copy. The local copy sees that it is on the local drive (C:\), so it proceeds to its normal startup process.

See if this method works for you.

 

[MsLady]

Hi bytemizer,
Thanks for this code, i have been searching around for something to update the new version i have on myserver to the local's user's computer.

I have tried with the code you have above, exactly as you explained it. The only thing it does it copy the db to the local drive, after the copy is done. If i have a new report or new change on the copy located on the server, how does this code check for that. It doesn't seem to be doing that. Please help?

 

[MsLady]

Actually, it even looks like it's still running directly from the server...

 

[ByteMyzer]

Okay, I guess I need to point out the painfully obvious. Look at the line of code:

Shell """" & SysCmd(acSysCmdAccessDir) _
            & "msaccess.exe"" " & """" _
            & fePath & """", vbMaximizedFocus

This tells Microsoft Access to load and run the database specified in the variable fePath, in this case, C:\Local Folder\MyDbFrontEnd.mdb.

If you have it set up right, the shortcut on the local computer points to the server copy. When the server copy is run, it copies itself to the local drive and runs it. When the local copy is run, it sees that it is the local copy and runs as intended.

This way, as long as the server copy is the copy with the most current updates, every time the shortcut is run, it copies the front-end from the server to the local computer and runs the local copy. Effectively, it updates itself every time it is run.

If there's any way I can make it any clearer, please let me know. I've used this approach extensively and found it to be the most effective means of keeping a front-end database up-to-date.

 

[john471]

What's wrong with a batch file ?

If you are going to copy from server to local each and every time it is run, why go to the trouble of opening the f/e from the server each time? Why not give the users a shortcut that goes to a batch/command file on the server to do the same job (i.e. copy f/e from server to local, then launch access using the new local copy). As far as the user is concerned the shortcut is to "the database" as the end result of running it is that the db appears

 

[ByteMyzer]

An excellent point, john471. The .BAT option works well in basic cases.

copy \\MyServer\Database\MyDbFrontEnd.mdb "C:\Local Folder\MyDbFrontEnd.mdb"
"C:\Local Folder\MyDbFrontEnd.mdb"

HOWEVER, suppose the shortcut calls to MSACCESS.EXE with Command-Line options?

For example:
"C:\Program Files\Microsoft Office\Office\msaccess.exe" /wrkgrp \\MyServer\Database\MySystem.mdw /cmd "my command text here"

This works ASSUMING that all the users have the exact same file path (C:\Program Files\Microsoft Office\Office\) to msaccess.exe on their local drives.

Conversely:
msaccess.exe /wrkgrp \\MyServer\Database\MySystem.mdw /cmd "my command text here"

...doesn't work from the .BAT file.


Consequently, if you want a method that works, regardless of the path configuration of any given installation of Microsoft Access, the method I outlined works every time.

 

[MsLady]

Okay, I guess I need to point out the painfully obvious. Look at the line of code:

Shell """" & SysCmd(acSysCmdAccessDir) _
            & "msaccess.exe"" " & """" _
            & fePath & """", vbMaximizedFocus

This tells Microsoft Access to load and run the database specified in the variable fePath, in this case, C:\Local Folder\MyDbFrontEnd.mdb.

If you have it set up right, the shortcut on the local computer points to the server copy. When the server copy is run, it copies itself to the local drive and runs it. When the local copy is run, it sees that it is the local copy and runs as intended.

This way, as long as the server copy is the copy with the most current updates, every time the shortcut is run, it copies the front-end from the server to the local computer and runs the local copy. Effectively, it updates itself every time it is run.

If there's any way I can make it any clearer, please let me know. I've used this approach extensively and found it to be the most effective means of keeping a front-end database up-to-date.

Thanks Byte Mzyer

It sounds like a very good idea. The runing the batch file idea isn't bad either. I have implemented that into my db and that works perfectly, from this thread http://www.access-programmers.co.uk/forums/showthread.php?p=465058#post465058.

However, i won't throw away this idea. I'll try it again. Might come in handy in the future. I have one question though, does it slow anything down, since it has to check and copy from server each time it runs?

 

[john471]

ByteMyzer,

True: I do use that method only for very basic cases AND all of my users have PCs with a standard image - same version of MSAccess installed in the same path.

For my most complicated case, I keep two copies on the local drive. Users launch a batch file which copies first copies local2 overtop of local1, then launches local1. When launched, the f/e checks the tables on the b/e to see if it is a current version. If it is not a current version, it copies the server f/e (current) version down to Local2, and advises the user to exit and re-launch to get the new version. (which of course means the new local2 is copied over to local1, and local1 is run). I did this because I was concerned about the +/-9MB f/e traversing the LAN every time a user launched it, as would be the case with your setup. Also reduces the likelihood that someone will be trying to copy-down the server f/e when I am wanting to "publish" a new version.

Regards

John.

 

[Patrick Brydone]

Thanks for all of the great suggestions however the suggestions given would create a lot of unnecessary traffic across the network each time users click to open up the database.

 

[ByteMyzer]

Well, Patrick, from your tone, it sounds more like you are trying to find a problem with the solution, rather than try a solution to the problem. On a typical LAN, the time and bandwidth usage to run any of the methods proposed in this thread are minimal, and virutally invisible to the end-user. Unless your front-end is a MAJOR-megabyte amount in size, I wouldn't even consider it an issue.