Update database front ends…

Everyone who has split a database needs a way to update the Frontends. There are a few choices out there…

Tony’s Auto FE Updater
BTAB Development Front-End Auto-Update Enabling Tool
Automatically Deploy a New Access Client

…and here’s another one:
In Backend (and this can be an SQL Server) create a table…
tblVersionControlMaster
vcmVersionControlID (PK, AutoNumber)
vcmVersion (Number, Double)

In the Frontend where the forms, queries, etc. reside create table…
tblVersionControlLocal
vclVersionControlID (PK, AutoNumber)
vclVersion (Number, Double)

Then create a link to the table in the Backend. It should look like this…

If on a Networked Drive

If on an SQL Server

Each table must have a matching PK, so, if tblVersionControlMaster has a PK of 1 then so must tblVersionControlLocal. That is needed for the UPDATE query to work. Once the tables are created, place a 1 on vcmVersion and vclVersion. That will become your starting number. If you want to start at another number that is fine, just make sure they are the same in both tables. You then need a folder on the Network, that everyone has the necessary permissions to, that will hold the updated Frontend.

Now, we want to create a message to let the Users know what is going on. Open your database and go to Design Mode of your Main Menu (or Switchboard) and create a Label naming it lblVersionControl. Leave the Caption blank, if the Caption won’t let you leave it blank, add a period, save and then go back and remove the period. I made my font color Red (#BA1419) but you can make yours whatever color you want. Next…

…go to the VBA Editor behind the Main Menu (or Switchboard). At the top of the Form Module place…

Private Sub Form_Current()

'Optional
'This is to put the Version in the Caption of the Main Menu or Switchboard
'For more tips http://www.access-diva.com/tips.html
Me.Caption = "Main Menu version " & DLookup("vclVersion", "tblVersionControlLocal") & ".x"

End Sub

Private Sub Form_Open(Cancel As Integer)

'Because the Frontends will be sent via eMail and/or ftp
'you will want the one you send you UPDATE the tblVersionControlMaster
'For more tips http://www.access-diva.com/tips.html

Dim strSQL As String

strLocalVersion = DLookup("vclVersion", "tblVersionControlLocal")
strNetworkVersion = DLookup("vcmVersion", "tblVersionControlMaster")

If strLocalVersion= strNetworkVersion Then
     strSQL = "UPDATE tblVersionControlLocal INNER JOIN tblVersionControlMaster " & _
               "ON tblVersionControlLocal.vclVersionControlID = tblVersionControlMaster.vcmVersionControlID " & _
                "SET tblVersionControlMaster.vcmVersion = [tblVersionControlLocal]![vclVersion]"
              CurrentDb.Execute strSQL, dbFailOnError
End If

End Sub

Private Sub Form_Load()

'This part that checks if there is a newer Frontend on the Network
'If there is one, the message will display informing the User
'For more tips http://www.access-diva.com/tips.html

strLocalVersion = DLookup("vclVersion", "tblVersionControlLocal")
strNetworkVersion = DLookup("vcmVersion", "tblVersionControlMaster")End Sub

If strLocalVersion = strNetworkVersion Then
     lblVersionControl.Caption = "Your version is out of date... EXIT, count to 10 and reopen!"
Else
     lblVersionControl.Caption = ""
End If

End Sub

Private Sub Form_Unload(Cancel As Integer)

'This part calls the .CMD file that will actually do the copying
'For more tips http://www.access-diva.com/tips.html

strLocalVersion = DLookup("vclVersion", "tblVersionControlLocal")
strNetworkVersion = DLookup("vcmVersion", "tblVersionControlMaster")

If strLocalVersion = strNetworkVersion Then
     Shell "c m d /c ""C:\Folder\update.c m d"",vbHide"
     '***Extra spaces in C M D, remove after copy/paste***
End If

End Sub

Now to create the file that actually does the work, open Notepad and copy/ paste…

Echo Off
ping 1.1.1.1 -n 1 -w 2000
COPY /Y "\\ServerName\Folder\YourNEWFrontEnd.accdb" "C:\Folder\YourOLDFrontEnd.accdb"
REM START "Msaccess.exe" "C:\Folder\YourNowNEWFrontEnd.accdb"

Make sure you change the names to match your Server\Path\FrontEnd.accdb. Once you are done, save, naming it Update.cmd and place in the same folder as the Frontend, for everyone except your primary User.

Now, you will need a primary User on the other end to send the updated file to, I generally pick a Manager or Supervisor. Send the file to them and tell them to open and close, making sure they detach from eMail and place in their Local folder first. This is done so that the UPDATE query in the On_Open event tells tblVersionControlMaster that there is a new version. Immediately, after closing, the file must be copied out to the folder on the Server… everyone else’s Frontend will automatically update.

Enjoy!

 3,258 total views,  1 views today