Prevent application from closing when New Access.Application sub exits.

[gearryg]

We use an Acess Database as a menu of Access Databases. It worked fine until we switched to Office 2007 from 2003. Code is below:

Private Sub DisclosureQAOPen_Click()
On Error GoTo Err_DisclosureQAOPen_Click
 
    Dim AccApp As Access.Application
    Dim str As String
 
    str = "\\Fanniemae.com\corp\DC\Shared\MF\MBS\GenDisc\Monitoring\QA\Disclosure QA.mdb"
    Set AccApp = New Access.Application
 
    With AccApp
    .OpenCurrentDatabase str
    .Visible = True
    End With
 
 
Exit_DisclosureQAOPen_Click:
    Exit Sub
 
Err_DisclosureQAOPen_Click:
    MsgBox Err.Description
    Resume Exit_DisclosureQAOPen_Click
 
End Sub

==========================================
The requested database closes at "Exit Sub" step under Exit_Disclosure QAOpen_Click:

We do not want the requested database to closed until closed by the user. Then return to Access Menu database.

[TheSmileyCoder]

I am not sure this will work, but try just before the exit:

Set accApp =Nothing

[ADezii]

You need to 'Widen the Scope' of the AccApp Variable, either Privately in the Form's Class Module, or Publically in a Standard Code Module. One the Click() Event is exited, AccApp has no meaning.

[NeoPa]

It closes because AccApp is declared on line #4 within the procedure. This variable has a scope which is local to that procedure so when the procedure exits the variable is deleted and cleared up (which includes closing the application held within it).

You need to ensure the variable is declared somewhere that has adequate scope for how you're planning to use it. In this case I would suggest a Public variable in either a Standard Module or a Form Module you know won't be closed while you still need the opened application.

Some basic instructions for using Application Automation.