After launching access from excel, I stop getting normal warning messages "Do you want to save changes"

Posted on 2009-04-21
Last Modified: 2012-08-14
I use the following code to launch Access from Excel.

    Lpath = "c:\myDB.mdb"
    Set AccApp = CreateObject("Access.Application")
    AccApp.OpenCurrentDatabase lpath
    AccApp.docmd.setwarnings True  '<==== THIS DOES NOT WORK EITHER

    AccApp.Visible = True
    AccApp.Application.Run "startupFromExcel"   ' call a procedure named "startupFromExcel".
    Accapp.DoCmd.SetWarnings True   '<===THIS DOES NOT WORK  EITHER

In most respects the resulting Access instance works just as if it was launched from a normal desktop icon.

I can make typical development modifications, like changing a form or a query.

The only problem is that the Access instance acts as if I am running under docmd.setwarnings False.

Every time I close a query, it is saved without the "do you want to save changes" warning.

Does anybody have any suggestions of how to rectify this automatically.

For instance, I tried putting the following code into my Excel and Access modules, BUT THEY DO NOT WORK I still do not get warning messages.

    sub startupFromExcel()
    docmd.setwarnings true  'DOES NOT WORK
    end sub

Even if I close Access, then reopen it manually, the docmd.setwarnings False   persists.

The only way to rectify things is to close Access, then use task manager to kill all instances of Access, then reopen Access.

This is very annoying, because it causes me to accidentally make changes to things without realizing it.

I considered having my excel macro automatically kill every existing Access process, but that will not help much, and it strikes me as a little dangerous.

Hope someone can come up with an answer.

Question by:rberke
    LVL 4

    Expert Comment

    Have you tried, Application.DisplayAlerts = True?

    Here is more info:

    Hope this helps.
    LVL 5

    Author Comment

    That doesn't work in Access, it gives a compile error.

    And, running it in excel only affects the excel environment.  Access continues to malfunction.

    LVL 4

    Expert Comment

    In doing some reading on this it is suggested to put:
    DoCmd.SetWarnings False

    prior to calling anything else, and then add DoCmd.SetWarnings True where and when you need it.

    Have you tried that approach?
    LVL 5

    Author Comment

    "put it where I need it" seems a little vague since I have already put it various places that don't work.

    But I tried a few other places with no success.

    If you have any more ideas, you might use the attached demo to see if your ideas work.

    Simply put db1test.xls and db1.mdb into c:\. Then open the excel file and push the button.

    LVL 5

    Author Comment

    i also played with application.getoption and application.setoption.

    But all the confirm action options were already on, so those are not the source of the problem
    LVL 4

    Expert Comment

    Thanks for attaching the db.  I will do some testing and see what I can come up with.
    LVL 5

    Accepted Solution

    I found a solution with the help of another forum.

    Private Sub CommandButton1_Click()

        lPath = "c:\db1.mdb"
    ' Eventually, we can delete this code
    ' Depending on how many things go wrong, there can be left over
    ' processes even when all databases have been closed.
    ' just to keep things clean while testing, we kill these leftover msaccess instances

        On Error Resume Next
        Set accapp = GetObject(, "access.application")
        If Err = 0 Then
            Set accapp = Nothing
            GoTo again
        End If
        On Error GoTo 0
    '  the above code can be deleted.

        Set accapp = CreateObject("Access.Application")
        accapp.OpenCurrentDatabase lPath

        accapp.Visible = True      ' to avoid run time error 2455
        accapp.usercontrol = True  ' setting usercontrol must come after setting visible

        accapp.Application.Run "startupFromExcel"   ' call a procedure named "startupFromExcel".
        MsgBox "access now works"
    End Sub

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now