Results 1 to 5 of 5
  1. #1
    brharrii's Avatar
    brharrii is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Seattle, WA
    Posts
    63

    Automate Front End update when version doesn't match

    The following thread got me a good start on automating my front end database deployments.

    https://www.accessforums.net/code-re...end-33331.html

    I took the code pretty close to the way it was provided on this thread, making changes where necessary, and assigned it to the on open event of a form that loads when the datbase is opened. Here is what my code looks like:

    Code:
       If Me.txtVersion.Value <> Me.lblVersion.Caption Then
                'copy Access file
                CreateObject("Scripting.FileSystemObject").CopyFile _
                "\\Seafile\Workgroups\Bartender Labels - All Locations\zz-Test back end Here\LabelsTest Version Control.accdb", "c:\", True
                'allow enough time for file to completely copy before opening
                Dim Start As Double
                Start = Timer
                While Timer < Start + 3
                DoEvents
                Wend
                'load new version - SysCmd function gets the Access executable file path
                'Shell function requires literal quote marks in the target filename string argument, hence the quadrupled quote marks
                Shell SysCmd(acSysCmdAccessDir) & "MSAccess.exe " & """" & CurrentProject.FullName & """", vbNormalFocus
                'close current file
                DoCmd.Quit
            End If
    In order for the code to work I've created a label on my form on the front end that stores the version number and also added a text box that pulls the version number from a table on the back end. When these two numbers match, the sub ends. When they don't match, the assumption is that there is an available updated front end on the server that is ready for to be copied to the work station. The code looks for the new server side front end and replaces the old work station front end with it.

    So far, when the version numbers match nothing happens (as is expected) but when the version numbers don't match strange things happen.

    The server side front end has managed to get copied to the work station pretty much every time this code runs, but probably 2/3 of the time seems to be corrupt. Usually I'll get an error message that says:

    Unrecognized database format 'C:\LabelTest Version Control.accdb'
    followed immediately by another error:

    The expression On Open you entered as the event property setting produced the following error: Module not found.
    When I close the database and re-open it, I get the following error:

    Microsoft Access has detected that this database is in an inconsistent state, and will attempt to recover the database...."
    Revovery hasn't been successful yet, and i haven't been able to recover the front end so I've been going back and manually copying it back over from a backup. Does anyone have an idea what might be causing this?

    Thanks



    Bruce

  2. #2
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    My first question would be, have you made sure that no one can be in the Front End Master when the copy occurs?

    My second is, compare your code to Bob Larson's free auto updater tool http://www.btabdevelopment.com/ts/freetools.

    I'm suspicious that you are running the entire copy script while Access is still open, rather than beginning a script with a delay, closing Access, letting the script complete the copy and restart Access. That just seems risky.

  3. #3
    brharrii's Avatar
    brharrii is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Seattle, WA
    Posts
    63
    I'm just testing right now and I know that no one has been in the master copy while I've been testing, but that will be an important consideration when it comes time to deploy the database, thank you for the suggestion.

    Also, I'll take a look at Bob Larson's tool.

    Thanks again

  4. #4
    brharrii's Avatar
    brharrii is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Seattle, WA
    Posts
    63
    Wouldn't you know it, my work machine blocks Bob Larson's site.... :P

    I'll take a look when I get home.

  5. #5
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    I feel ya, bro.

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

Similar Threads

  1. Replies: 3
    Last Post: 03-12-2013, 01:38 PM
  2. Access Version update
    By newwales in forum Access
    Replies: 0
    Last Post: 11-02-2011, 05:35 AM
  3. update front end mde
    By slimjen in forum Access
    Replies: 2
    Last Post: 10-31-2011, 11:37 AM
  4. Replies: 13
    Last Post: 03-28-2011, 02:09 PM
  5. Finding data that doesn't match
    By dlhayes in forum Queries
    Replies: 1
    Last Post: 11-11-2006, 08:14 PM

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