Database version update feature. 3

[claytonjgordon]

How do you create a database update feature that updates the various front ends to the latest version?

To clarify: As a noob, my normal method for distributing my database is to send them the front end via Email attachment. Every time I change the database I need to send everyone a new attachment. I’ve seen other people create instillation buttons that run a wizard that adds the database to their start menus for them. More importantly, I’ve seen people create update buttons so that they can press the button and it will automatically install the latest version of the database from a share drive on the network.

This is obviously a lot better than my method. How difficult is this to accomplish? Can anyone provide some details on how this is done?



Dominus Nihil
(Master of Nothing)

 

[dhookom]

You might want to check out Tony Toews' solution at

http://www.granite.ab.ca/access/autofe.htm.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[FancyPrairie]

This is how I did it. I created a table that contains the name of the computer and a flag that indicates whether or not a new version of the database is available. (My table also contains other info such as the name of the primary user of the pc, thier phone number, office location, and whatever else I need to know...comes in handy when I need to know who's logged into my db so I contact them for whatever reason. It also contains a flag to indicate that a message should pop up on the user's machine..."System going down in 4 minutes."). But you can include/exclude whatever fields you want. You just need the computer name and NewVersion flag. By setting it up this way, you can specify which group of users get the new version (i.e. might have a group of users that are your testers, for example. Or one of your user's might be having weird problems with the FE and so you can download a new version to just that machine.

Anyway, at startup I check to see if the pc has been registered. If not, I popup a form that asks the user to register their computer (fill in all of the info I need).

Also at startup, the program checks to see if the NewVersion flag for that pc is set. If so, a batch job is launched to copy the new version down to the user's machine and relaunch the database.

Note that I have a form (visible property = false) running on a timer. So every 1 minute (or whatever) it checks to see if the BroadcastMessage flag is set. If so, then it pops up a form and displays the message I want the user to see (i.e. New version available, exit db and relaunch it).

 

[claytonjgordon]

Great feed back, both of you. I just got a new project with a higher priority a few minutes ago; I'll have to examine your posts more closely when I'm able to attack this database again.

Thank you both for your help.


Dominus Nihil
(Master of Nothing)

 

[danvlas]

I'm using a batch file that always copies the front end from a known location... no harm done, works every time.

copy "\\server\Path\File.mde" "C:\Program Files\YourProgramPath\File.mde"
"C:\Program Files\Common Files\Microsoft Shared\Access Runtime\Office10\MSACCESS.EXE" /Runtime "C:\Program Files\YourProgramPath\File.mde"
Exit

All I have to do is re-place the new front-end, which I usually do with ftp. All the rest is done by the batch file...

FancyPrairie's solution looks good as it is fully automatic.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[JeremyNYC]

My method is detailed in the developer's section of

http://www.abcdataworks.com.

---
Jeremy Wallace
METRIX Project Coordinator
Fund for the City of New York

 

[sueb859]

FancyPrarie,
Are you doing all of this checking from Access? Are you using linked tables?

Are you able to share any of your code?

What I've been trying to do is fiqure out a way to determine if an mdb is the latest version without having to open the database to look at a table which stores the version. I use a VB exe to check for the most recent version but it does not check for anything else.

Sue

 

[FancyPrairie]

Yes, I'm doing the checking from Access.
Yes, I'm using linked tables.

I created a library database (FE and BE) years ago and have been adding to it as needed. One of the tables in this database is the table that keeps track of who is using my databases.

The table looks something like this (off the top of my head):

strDatabaseName Text Field (This is the name of the Front End Database (user could be using more than one of my databases)
strComputerName Text Field
strPrimaryUserName Text Field (=CurrentUser)
strOfficeLocation Text Field
strPhoneNumber Text Field
ysnNewVersion Yes/No
ysnBroadcastMessage Yes/No
ysnShutDown Yes/No

Note that I have another table that defines everything I need to know about the database. That is, who requested it, what's its purpose, etc. Consequently, when populating the table above, strDatabaseName can be a combobox pulling from this table. This table provides many benefits later on in maintaining my databases.

I've created a Startup function in my library that all of my databases call.

1. The first thing it does is checks to see who the user is. If it's me, then it checks to see if the project is registered. If not, it pops up a form that forces me to register (document) the project so I don't forget.

2. All of my forms have a label on them indicating whether I'm linked to the "Live" or "Test" database. So this startup routine determines which database I'm linked to and stores it in a global variable for use by my forms. When the form opens and the user is me, then the label (or form's caption property) is set to Live or Test and made visible, else it's hidden from the user. This is done to prevent me from accidently updating live data.

3. Opens the table see if the computer has been registered. If not, it pops up a form, in my library, that asks the user to register the computer (i.e. Primary user name, phone number, office location).

4. Checks to see if the NewVersion flag is set. If it is, the database launches a job and the database exits. The job copies a new version to the user's machine and starts the database backup again. It also clears the NewVersion flag.

5. Checks to see if the Shutdown flag is set. If so, then the user is not allowed to get into the database at this time. I also have an invisible form running on a timer that checks to see if this flag is set (or the broadcast flag). If this flag is set then my invisible form issues an Application.Quit command, thus forcing the user out of the database. If the broadcast flag is set, a form in my library is popedup and displays the message I want the user to see (i.e. System going down in 5 minutes, please exit).

Having this information really comes in handy when trying to maintain multiple databases with several hundred users. But, regardless, if you have to put your database on more than one machine, it is still worth the effort of putting the above tables, etc. together. You don't have to do it all at once, just as needed.

And, no I can't give you the code because it is to well integrated in all my other stuff. So it would take me too long to strip out what you wouldn't need at this time (but would at some later date). Sorry

 

[sueb859]

I've not used a library database. Is the library database on a network? Do they need a reference set to the library database? Does the user need anything special on the computer to make this work? How do you refer to functions in your library which are run?

Not something I've worked with yet but sounds interesting.

Sue

 

[FancyPrairie]

A library database is just another database (usually you change the file type to .mda rather than .mdb). And, yes, the library database is located on the network. And, yes, you simply set a reference to it. The user does not need anything special on their machine to make it work.

Since the library database is referenced in your database, you simply call the functions as if they resided in your current database.

One thing you might consider is using a naming convention for all of your objects within the library database. For example, if you were working for IBM you might use that as a prefix for your objects. For example, frmIBM_RegisterComputer, IBM_GetComputerName, etc. Another thing to note is that if you want to open a form that resides in your library databae, you can't use the DoCmd.OpenForm. Rather you will need to create a function within your library database that issues that command (i.e. IBM_OpenForm) passing the same arguments as the DoCmd.OpenForm method uses.

Another thing, prior to releasing your databases to the user, you would be better off saving your library database as a .mde file (smaller and user's can't see or mess with your code, plus other reasons) and referencing the .mde file rather than the .mda file.

Finally, you will need to build your database slowly. Because most people don't have the time to put it all together at once and you don't know what you are going to need. But when you build a function, think about whether or not it could be used by other databases. If so, move it to your library.

 

[mustangcoupe]

OK, I downloaded some sample code off the web (I dont know where) but here is what I use for FE updating.
create 2 new tables tblVersionClient and tblVersionServer
both with one field called VersionNumber
keep tblVersionClient in the FE link tblVersionServer
from the BE.

this code is in the "splash screen(form)" shown on startup.

Option Compare Database
Option Explicit

Private strVerClient As String
Private strVerServer As String

Private Sub Form_Load()
On Error Resume Next

        strVerClient = Nz(DLookup("[VersionNumber]", "[tblVersionClient]"), "")
        strVerServer = Nz(DLookup("[VersionNumber]", "[tblVersionServer]"), "")
        Me.Repaint

   
End Sub

Private Sub Form_Timer()
On Error Resume Next

    Const q As String * 1 = """"
    
    Dim strMsg As String
    Dim strPath As String
    Dim strUpdateTool As String

    Me.TimerInterval = 0
        
    If strVerClient = strVerServer Then
        Me.Visible = False
        DoEvents
'change thi to your "first form"
        DoCmd.OpenForm "job_form"
    Else
        strMsg = "You do not have the latest version." & vbCrLf & vbCrLf & _
                 "Would you like to download the latest client?"
        If MsgBox(strMsg, vbExclamation + vbOKCancel, "Update Client") = vbOK Then
        
            strPath = Left(CurrentDb.Name, LastInStr(CurrentDb.Name, "\"))
            strPath = ********location of update file here**********            
            strUpdateTool = "MSAccess.exe " & q & strPath & q
            
            Shell strUpdateTool, vbNormalFocus
            DoCmd.Quit
        Else
            MsgBox "You will be reminded next time you start the program.", vbInformation
            Me.Visible = False
            DoEvents
'next page to open.
            DoCmd.OpenForm "job_form"
        End If
    End If
    
End Sub

code from Update file
this is also in a "splash screen(form)" but

Option Compare Database
Option Explicit

Dim strPath As String
Dim strDest As String
Dim strBkup As String
Dim strMyDB As String
Dim strVer As String
    
Private Sub Form_Open(Cancel As Integer)
On Error Resume Next

        DoCmd.Hourglass True
        DoEvents
        
        ' Update status form to identify version being copied.
        strVer = DLookup("[VersionNumber]", "tblVersionServer")
        Me.txtVer.Caption = "Installing version number ... " & strVer
        ' Load variables with correct file name-path values.
        strMyDB = CurrentDb.Name
        strPath = Left(strMyDB, LastInStr(strMyDB, "\"))
        'Debug.Print strPath
        strDest = *****Current FE Location*******        
        strBkup = *****Current FE Backup Location(if you have one*******
        FileCopy strDest, strBkup
   
End Sub

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 = ****** New FE Version ******
    FileCopy strSource, strDest
    
    DoEvents
    
    ' Now that the new client file has been copied, it may
    ' be opened.  Use the SHELL command to accomplish this.
    strOpenClient = "MSAccess.exe " & q & strDest & q
    Shell strOpenClient, vbNormalFocus

    ' Cleanup the mouse pointer and exit from this application.
    DoCmd.Hourglass False
    DoCmd.Quit

End Sub

--Todd


TechnicalUser pretending to be a programmer(shhh… the boss doesn’t know yet)

 

[MichaelRed]

There are several variations on this theme sprinkled throughout the various fora in Tek-Tips. Many of the approaches have their own adherents. My choice is not mentioned in the above, but involves copying ONLY the changed objects to the app instance which needs to updated. Normally, I would just 'shoo' you to 'advanced' search with a few 'key words and tricky phrases', alas alack and awry, the search process is -perhaps- less robust than this and would probably not find the specific reference I would propose. see Thread181-30072 (it is somewhat old, but as far as i know it did wend it's way to a (in my opinion) quite reasobable soloution, requiring nothing more that 'good' dicipline and a link between a Master / Design copy of the app and hte various sesktop versions.






MichaelRed

 

[FancyPrairie]

I've never felt comfortable about storing the version number in a table. I felt that I could not be 100% confident that the FE the user had on their machine was the latest version. I had no way to verify it.

Consequently, I store the version number with the FE by placing it in the "Custom" Database Properties.

You don't need the following, but this is how I manage it. When I'm done making changes to the database and am ready to release it, I open a form that allows me to document what changes I made to the database. Upon closing the form, it updates the custom database property with the current date/time. Then I distribute the database to my users. Now I can be 100% positive that the when I check the version number (i.e. date/time) I know it is the one I delivered to them. (I have a Help About form that displays the version number (i.e. date/time) plus other things I/users need to know.)

 

[sueb859]

FancyPrairie,
Would you be able to share code where you set and retrieve the Custom property?
Sue

 

[FancyPrairie]

I wrote this code for Access 2.0 and Access 2000+ have more custom properties that you could utilize. With that said, I named my custom property "Revision Date". The following routine will set the "Revision Date" to either the current date/time or the date/time passed to it. If the custom property does not yet exist, this routine will create it.

Public Function UpdateRevisionDate(Optional varRevisionDate As Variant)

'*********************************
'*  Declarations Specifications  *
'*********************************

    Dim dbs As Database
    Dim ctrDatabases As DAO.Container             '"Database" Container
    Dim docUserDefined As DAO.Document            '"UserDefined" Document
    Dim prpUserDefined As DAO.Property            '"UserDefined" Property
    
    Dim strRevisionDate As String
    
'****************
'*  Initialize  *
'****************
    
    On Error GoTo ErrHandler
    
    Set dbs = CurrentDb
    Set ctrDatabases = dbs.Containers!Databases
    Set docUserDefined = ctrDatabases.Documents!UserDefined
    
    If (IsMissing(varRevisionDate)) Then
        strRevisionDate = CStr(Now)
    Else
        strRevisionDate = CStr(varRevisionDate)
    End If
    
'****************************************************************************************
'*  Attempt to set "UserDefined" property.  Note that if the property has not yet been  *
'*  created, then error 3270.  If error 3270, create property and populate it.          *
'****************************************************************************************

    docUserDefined.Properties("Revision Date") = CStr(strRevisionDate)

'*******************
'*  Exit Function  *
'*******************

ExitProcedure:

    Exit Function
    
'****************************
'*  Error Recovery Section  *
'****************************

ErrHandler:
    
    '*****************************************************************************
    '*  If Error 3270, then property does not yet exist.  Therefore, create it.  *
    '*****************************************************************************
    
    If Err.Number = 3270 Then
        
        Set prpUserDefined = docUserDefined.CreateProperty("Revision Date", dbText, strRevisionDate)
        docUserDefined.Properties.Append prpUserDefined
        Resume Next
    
    Else
        
        Err.Raise Err.Number, "UpdateRevisionDate", Err.Description

    End If

End Function

 

[claytonjgordon]

Good to see a lot of people got use out of this thread.

I ended up using Tony Toews' solution, the link provided in the first reply. I got it to work in a few hours of playing with it, which means, given my very limited experaince with all of this, that it's pretty easy to use.




Dominus Nihil
(Master of Nothing)