Running Code as Program Closes

My objective is to close the MDE so it can be renamed.  Here is what I've got.

------------
Private Sub butUpdate_Click()
Dim DirPath As String, RunFS
UpdateNow = 1
DoCmd.Close acForm, "Options"      'Form the button is on
FltStaUpdate
Application.Quit
End Sub

'Download Update file
Sub FSUpdate()

   Dim dldata As FileRegistryDownloadData
   With dldata
      .DownloadDlgTitle = "Flight Status Manager System Update"
      .DownloadRemoteFileUrl = "http://www.FltStatus.com/FltStaUpdate.exe"
      .DownloadTempRegKey = "C:\Program Files\FltStatus"
      .DownloadLocalFileName = .DownloadTempRegKey & "\" & "FltStaUpdate.zip"
      If DownloadRemoteFile(dldata) = True Then
          MsgBox "Download success!"
      Else
          MsgBox "Download failed or user pressed Cancel"
      End If
   
   End With
End Sub

'Here is the trouble.  Work fine in a Sub
Private Sub Form_Close()
If UpdateNow = 1 Then
    Dim DirPath As String, strDB
    Dim objAccess As Access.Application
    Set objAccess = New Access.Application
    DirPath = CurrentProject.Path
    strDB = DirPath & "\FSUpdate.mde"
    'objAccess.Visible = True
    objAccess.OpenCurrentDatabase strDB
End If
End Sub

---------------------------------------
The FSUpate.mde that is opened, rename the current MDE.  But I can not get it to work using the OnClose method in my main form.  My code in OnClose does not run on application.quit
buzzcarterAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Leigh PurvisDatabase DeveloperCommented:
Is UpdateNow a global variable?
Or just local to your form?
0
Rey Obrero (Capricorn1)Commented:

try this revision

Private Sub Form_Close()
If UpdateNow = 1 Then
    Dim DirPath As String, strDB

    Dim db As DAO.Database

    Dim objAccess As  New Access.Application

    DirPath = CurrentProject.Path
    strDB = DirPath & "\FSUpdate.mde"

    objAccess.Visible = True

    Set db = objAcess.DBEngine.OpenDatabase(strDB)
                 objAccess.OpenCurrentDatabase(strDB)


End If
End Sub
0
buzzcarterAuthor Commented:
Public UpdateNow
in module
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Rey Obrero (Capricorn1)Commented:
oops

    Set db = objAcess.DBEngine.OpenDatabase(strDB)
should  be

    Set db = objAccess.DBEngine.OpenDatabase(strDB)
0
buzzcarterAuthor Commented:
Capricorn1

No Joy.  Same failure to excute.
0
Rey Obrero (Capricorn1)Commented:
buzzcarter, can you be more specific.
0
Leigh PurvisDatabase DeveloperCommented:
Have you stepped through the code?  Is the code in that event running?
You say the same code works in a different event - but not on the form close?
0
harfangCommented:
Hello,

Apparently, you want to open a new instance of Access while closing the current one. Your code will not work, because when you exit your application, all variables are reset, which will also close the automated version of Access that you are opening.
To avoid this default (and very reasonable) behavior, you will need to add this line:

    objAccess.UserControl = True

This tells the new copy that is is no longer controlled programatically through a VB variable but by the user.

Good Luck
(°v°)
0
buzzcarterAuthor Commented:
LPurvis
 
On Stepping through the code runs until application.quit and the form close does not run.

I have tested the same code in a Sub Test() module adn it excutes correctly.

0
Leigh PurvisDatabase DeveloperCommented:
Markus has nailed it.

(And my forehead is still red and throbbing).
0
Rey Obrero (Capricorn1)Commented:

place the sub before Application.quit
0
buzzcarterAuthor Commented:
harfang

You are correct in I want to open a new instance of Access while closing the current one.

I though the the Form Close ran just before the access application ended.  Where should I place the code and the addition objAccess.UserControl = True?

Or maybe another solution, is there a way to check to see if the first.mde is running?  I could open the second.mde and not let the code excute in the second.mde until the first.mde had closed.
0
thenelsonCommented:
Does it work when you close the form?  If so, then the form needs to be open when the database closes.  Best to create a form that opens hidden when the database opens and remains open until the database closes.  

BTW: you can create a batch file to wait then rename the file and call the batch file when the database closes:
Private Sub Form_Close()
Shell WaitRename.Bat
End Sub

WaitRename.Bat:

REM Wait 30 seconds for the database to close (60 seconds = 60000, 120 seconds = 120000, etc.)
PING 1.1.1.1 -n 1 -w 30000 >NUL
Rem Rename
Ren OldPathName NewName
0
Leigh PurvisDatabase DeveloperCommented:
I think you've missed the point slightly buzzcarter.

Markus is saying that when your application closes the objects you've created are destroyed.
Since it is the only pointer to it (i.e. there's been no user intervention in the created instance) the new application instance will be destroyed too.
(It is indeed very reasonable behaviour - and more importantly, consistent).

The code he suggested allows the new instance to become under user control (i.e. won't be destroyed when your code pointer to it is removed).

Your app then closes - the other mde does what it needs to do.
0
buzzcarterAuthor Commented:
OK, I'm catching on.  

I removed the varible and the If statement as harfang point out and the Form Close now excutes.  But I still need a conditional statement.  I will try thenelson's suggestion and create a hidden form and open it on the condition, using the form close there.
0
harfangCommented:
This is my test module which I created before answering:

Private Sub Form_Close()

    If chkRestart Then
        'MsgBox "closing"
        With New Access.Application
            .Visible = True
            .OpenCurrentDatabase CurrentDb.Name
            .UserControl = True
            .DoCmd.OpenForm Me.Name
        End With
    End If
   
End Sub

chkRestart is a check box, but it could be a boolean variable (your "UpdateNow"). To get your behaviour, I guess you would replace CurrentDb.Name with:

    ... CurrentProject.Path & "\FSUpdate.mde"

I don't think I mentioned anything about the if statement...

Good Luck!
(°v°)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
buzzcarterAuthor Commented:
Thanks to All! I learned from everyone.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.