Need to keep track of versions of databases

[DougP]

I have 6 or so databases that do a myriad of things. We have technicians with laptops that use these. Since they are not always able to get on the internet they have a local copy. When they get back I made an Export form to gather the data. (I know that’s probably not the best way)
Any how if I made a new feature or fix a bug, not everyone gets it since they are in and out of the office on different schedules. I thought about a utility table to keep a number or something and then I could look at that. But I have to make sure I change it every single time. The date is no good since when you open it, it shows today’s date. I thought about replication but not sure if I like that or not. I would like to look at something and know quickly or have them look at something but have it update automatically like when you compile a VB app it will increment.

TIA

DougP
< I Built one

http://www.robots2000.com

 

[dhookom]

Do you have your application divided into front and back ends? If not, that is the first task to accomplish. Then, I would add a small version table in the front-end and primary back-end that contains the version number and date. If they are different, the user is prompted to download the latest version.

Tony Toews has an updater available for free at

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

Duane
Hook'D on Access
MS Access MVP

 

[DougP]

The ones that are on a laptop are not Front end, back end. Since they are not connected to our server.
There is a backend _be and the one we use in house has a front end to it. When the techs come in they open there database and the export form and the form keeps track if they uplaoded a particular job or not.

anyway I'll give it a try.

"...small version table in the front-end and primary back-end that contains the version number and date"


DougP
< I Built one

http://www.robots2000.com

 

[DougP]

Thanks dhookom , I'll try this and see if it works.
Here is the code I created to track it.
This is run from a form in the front end that loads using and autoexec macro. I created a table in the front end and one same one in the back end as dhookom mentioned. I Linked the backend table which is VersionThisdb1. So when they are in the office it is connected and checks. When they are out of the office an error is trapped and discarded.

Public Function CompareDBVersions()
    On Error GoTo Err_CompareDBVersions
    'this sub compares the version of the database to whats on the server
    ' when changes are made to the database this function notifies users.
    
    Dim Conn2 As ADODB.Connection
    Dim Rs1 As ADODB.Recordset
    Dim SQLCode As String
    Set Conn2 = CurrentProject.Connection
    Set Rs1 = New ADODB.Recordset
    SQLCode = "SELECT VersionThisdb.Version, VersionThisdb.Date " & _
              "FROM VersionThisdb1 INNER JOIN VersionThisdb ON VersionThisdb1.Version = VersionThisdb.Version;"
              
    Rs1.Open SQLCode, Conn2, adOpenStatic, adLockOptimistic
    If Rs1.RecordCount = 0 Then
        msg = "There is a newer version of the database" & vbCrLf
        msg = msg & "Contact Doug to update it"
        MsgBox msg, vbInformation, "Newer version of this database"
    End If

    
Exit_CompareDBVersions:
'  close it this way
    Set Rs1 = Nothing
    Set Conn2 = Nothing

Exit Function

Err_CompareDBVersions:
    Select Case Err.Number
        Case -2147217865
            'table Not Found, do nothing we’re not in the office.
            
        Case Else
            MsgBox "Error # " & Err.Number & "  " & Err.Description, vbInformation, "In function CompareDBVersions "
            
    End Select

    Resume Exit_CompareDBVersions
    
End Function

DougP
< I Built one

http://www.robots2000.com