Results 1 to 5 of 5
  1. #1
    gopherking is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011

    Validate User Current DB Version against Network DB Version

    I am in the process of attempting to create a means to automatically check to see if the user's local DB is the same version as the network DB. This is for my Front End DB only. Currently, we use a Run.mde file to copy the current database from the network location and then opens the local DB after copied. This is to ensure that all our users have the most current DB each day incase there are any changes (doesn't require me to notify everyone when a new DB is available for download). The problem is that my Front End database is looming around 40 - 50 mb in size and is taking some time to download.

    I plan to keep this Run.mde database in place but am trying to set it up to compare a .txt file on the local pc vs. a .txt file on the network location. The filenames of the .txt files would contain the database version number, for example: 2.0.00.DBVer.txt

    My initial thought was to setup a local and network variable to be populated with the results from a DIR("c:\local\folder\*.txt) but it seems that this doesn't work. Does anyone know of another command that I can use in VBA to return the current name of a file within a specific folder?

    The idea would be that if both files have the same names that the database would not be copied and the existing DB would be opened (if it exists, if not it would need to copy the DB), if not, copy the new database and .txt files to the local PC and run. This would save users time in that they don't need to download the database each time they open it.

    Note, I do plan to store the .txt files in folders that are created by the Run.mde, this way if they are deleted, they will be recreated on the next run of the database.

    Any help would be greatly appreciated. Thanks!

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Nevada, USA
    Personally I use a local table in the front end and just compare the max version number in the two copies (I create a new record with the date, version number, and a summary of the changes I made for each version). Bill has a method here that sounds closer to what you're doing:
    Paul (wino moderator)
    MS Access MVP 2007-2019

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    The Great Land
    And I do something quite similar to pbaldy:
    How to attach file: To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    gopherking is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    And I thought there was a simple work around for this ... Well, looks like I have some digesting to do on the different solutions you provided. Thanks Paul and June7, I will implement one of these in the near future (I hope!! Have some major projects due in the next week that have priority) ...

  5. #5
    gopherking is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    So, I had decided to go with a VBScript to determine if the current DB on a users local PC is current or not. The entire process requires the VBScript, a .txt file to house the current DB Version information, and a .hta file to display a SplashScreen so users know something is happening while the script is running.

    Upon executing the VBScript, one of the first items called is the SplashScreen, which is an .hta file (which is an HTML driven file). Below is the code I used in my .hta file, but it can be modified as you need:

      <script language = "VBScript">
       Sub Window_OnLoad
        window.resizeTo 400,250
        idTimer = window.setTimeout("PausedSection", 10000, "VBScript")
       End Sub
       Sub PausedSection
       End Sub
      <title>DB Version Check ...</title>
     <body style="background-color:#4f81bd; text-align:center;">
      <p style="font-family:Verdana; font-size:12pt; color:white; font-weight:bold;">Please wait while we check for the latest version of the Database ...</p>
      <br />
      <img src="Open.gif" alt="" />
    Next, you will need to create a .txt file that contains only a single line in it. The line that I use is: VER=x.x.xx (where the x's are the current version number). In the VBScript, it does a comparison of the lines inside of the .txt files on the user's local PC and the Network location where the master .txt file is stored. If they are the same, then the VBScript simply opens the existing FE DB on the users PC. If they are different, the VBScript copies the new DB to the users PC prior to opening it.

    Finally, the VBScript that I setup is as follows (feel free to use and modify as you see fit):

    ' Project  : Database (Access 2007)
    ' Title   : ERDB.vbs
    ' Date   : 2012/04/05
    ' Author  : gopherking
    ' Purpose  : Validates the current DB Front End version on the user's PC with the
    '        network version and then either opens the existing DB or copies the 
    '        current DB to the user's PC and opens it.  The file to confirm the DB
    '        version is in the format of DBVer.txt and should be located in
    '        the user's local Documents folder
    ' Release Notes:
    '  2012/04/05 - Initial creation of script file
    Option Explicit
    'Version Text File Name
    Const VerFile = "DBVer.txt"
    'Local Front End DB name
    Const cLOCFE = "FE_DB.accde"
    'Network path for DB and Version file
    Const cSVRPath = "\\EnterYour\Network\Path\Here"
    'Network Front End DB name
    Const cSVRFE = "DB_FrontEnd.accde"
    'Name used for Database
    Const cAppName = "My Database"
    'Use any gif you would like here
    Const cAniGif = "graphics\open.gif"
    Const ForReading = 1
    Dim WSHShell
    Dim fs
    Dim oIE
    Dim LocVer
    Dim SvrVer
    Dim cUser
    Dim cLOCPath
    Dim WshNetwork
    Sub GetDB()
     If fs.FileExists(cLOCPath & "\" & VerFile) Then
      fs.DeleteFile(cLOCPath & "\" & VerFile)
     End If
     If fs.FileExists(cLOCPath & "\" & cLOCFE) Then
      fs.DeleteFile(cLOCPath & "\" & cLOCFE)
     End If
     fs.CopyFile cSVRPath & "\" & VerFile, cLOCPath & "\" & VerFile, True
     fs.CopyFile cSVRPath & "\" & cSVRFE, cLOCPath & "\" & cLOCFE, True
     'pause the script to allow for DB to download before opening ...
     WScript.Sleep 5000
    End Sub
    Sub GetVer(vType)
     Dim objRegEx, objfile, colMatches, strSearchString, strMatch
     Set objRegEx = CreateObject("VBScript.RegExp")
     objRegEx.Pattern = "VER="
     If vType = "LOC" Then
      Set objFile = fs.OpenTextFile(cLOCPath & "\" & VerFile, ForReading)
      Set objFile = fs.OpenTextFile(cSVRPath & "\" & VerFile, ForReading)
     End If
     Do Until objFile.AtEndOfStream
      strSearchString = objFile.ReadLine
      Set colMatches = objRegEx.Execute(strSearchString)
      If colMatches.Count > 0 Then
       For Each strMatch in colMatches
        If vType = "LOC" Then
         LocVer = strSearchString
         'WScript.Echo "Local Version: " & LocVer
         SvrVer = strSearchString
         'WScript.Echo "Server Verion: " & SvrVer
        End If
      End If
    End Sub
    Sub SetVars()
     Set WshNetwork = WScript.CreateObject("WScript.Network")
     'WScript.Echo "Current User Name: " & WshNetwork.UserName
     cUser = WshNetwork.UserName
     'WScript.Echo "cUser Variable: " & cUser
     cLOCPath = "C:\Users\" & cUser & "\Documents\"
    End Sub
    Sub main()
     'Set Global Variables that are not Constants
     Call SetVars
     Set WSHShell = WScript.CreateObject("WScript.Shell")
     Set fs = WScript.CreateObject("Scripting.FileSystemObject")
     'Open the splashscreen so they know something is going on 
     If fs.FileExists(cLOCPath & "\" & VerFile) Then
      Call GetVer("LOC")
      If fs.FileExists(cSVRPath & "\" & VerFile) Then
       Call GetVer("SVR")
       If LocVer <> SvrVer Then
        'MsgBox "LocVer and SvrVer Diff; Getting the latest DB"
        Call GetDB
       End If
        MsgBox "An unexpected Error has occurred.  Your existing database will be " & _
         "opened at this time." & chr(13) & chr(13) & "Please report this error by taking " & _
         "a screen shot of this message and email it to the Administrator." _
         & chr(13) & chr(13) & "Error:  Missing Server Version File" & chr(13) & _
         "Description:  The expected file was not found in the specific network " & _
         "location",0 & 16,"Missing Network Version File"
        If fs.FileExists(cLOCPath & "\" & cLOCFE) = False Then
         On Error Resume Next
         'MsgBox "No Local FE DB; Getting the latest DB"
         Call GetDB
        End If
      End If
       'MsgBox "No Local Version File; Getting the latest DB"
       Call GetDB
     End If
     'MsgBox "opening the local DB"
     'Open the DB on the PC
     WSHShell.Run cLOCPath & "\" & cLOCFE
     Set fs = Nothing
     Set WSHShell = Nothing
    End Sub
    'VBScript Starting Point
     Call main
    I hope that this can help others as it definitely helped me.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 01-18-2012, 12:46 PM
  2. Version Updates
    By avarusbrightfyre in forum Access
    Replies: 1
    Last Post: 09-09-2010, 11:16 AM
  3. What version of VB does Access use
    By zapper222 in forum Programming
    Replies: 1
    Last Post: 08-03-2010, 03:54 PM
  4. Academic version
    By steele in forum Access
    Replies: 1
    Last Post: 12-03-2009, 05:12 PM
  5. Version Control
    By martinjamesward in forum Access
    Replies: 0
    Last Post: 08-26-2009, 02:01 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums - Senior Forums