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

[RBerke]

In Excel, I use the following code to launch Access from Excel.

Private Sub CommandButton1_Click()

lPath = "c:\db1.mdb"
again:
' 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, we kill any leftover access instances
' BUT, this is just temporary to keep testing clean.
' The solution I desire would NOT require killing old processes.
'
On Error Resume Next
Set accapp = GetObject(, "access.application")
If Err = 0 Then
accapp.Quit
Set accapp = Nothing
GoTo again
End If
On Error GoTo 0

Set accapp = CreateObject("Access.Application")
accapp.DoCmd.SetWarnings False
accapp.OpenCurrentDatabase lPath
accapp.DoCmd.SetWarnings True '<==== THIS DOES NOT WORK EITHER

accapp.Visible = True

accapp.Application.Run "startupFromExcel" ' calls a procedure named "startupFromExcel".
accapp.DoCmd.SetWarnings True '<===THIS DOES NOT WORK EITHER
End Sub


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?

For instance, I tried putting the following code into my Access module, BUT IT DOES NOT WORK !! I still do not get warning messages.

sub startupFromExcel()

docmd.setwarnings true 'DOES NOT WORK
.......
end sub


So, I conclude that the symptom may ACT as if Setwarnings is false, but the actual mechanism may be totally unrelated to Setwarnings.

I also played with application.getoption and application.setoption, but it turns out that all of the "confirm action" options were already on, so those are not the source of the problem.

Even if I close Access, then reopen it manually, the docmd.setwarnings False persists -- because the MSACCESS process was still running.

The only way to rectify things is to close Access, then use task manager to kill all instances of Access, then reopen Access manually, not from the VBA code.

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 does not help.


I am out of ideas and hope someone can come up with an answer.

 

[xenou]

Hi,
The thought that comes to mind immediately is why are you making design changes at runtime? What are you doing that creates these changes? As far as the behavior you describe, not sure. Is the application visible? Since an invisible application can't show messages to a user maybe this overrides the warnings setting? I'm just shooting in the dark though.

Alex

 

[RBerke]

This is a prototype system, it works well enough to be useful, but I make changes to it regularly. Other users get the mdb in c:\production\, but my computer always goes to c:\test\ so it is safe.

The bulk of the system is in Excel, and links to Access when needed. For instance when I am in an excel spreadsheet that shows an sales pivot table, I can select a customer in the table, then click on a button. The excel code opens an access userform showing that customer's contact information. I can then do an f11 and make changes to the form or modules so that it is closer to our goals. And, I do not even lose the position in the open form. It is significantly easier than closing out of access, reopening the data base browsing to the correct form, then accessing that same customer.

My exact problem can be demonstrated in much simpler system. The entire vba excel code was in my original post. The vba access code could as simple as this

Sub startupFromExcel()
DoCmd.SetWarnings True ' <==== DOES NOT HELP

MsgBox "modify a query and see what happens"
DoCmd.OpenForm "form1"

DoCmd.SetWarnings True ' <=== DOES NOT HELP

End Sub

 

[xenou]

Yes, I can reproduce that behavior as you describe it. Not sure why set warnings seems to have no effect. I was thinking you could use a close event to discard changes (DoCmd.Close acForm, Me.Name, acSaveNo) but it appears you sometimes want to save changes, sometimes not. Perhaps you need to add a msgbox to the close event to..."Do you want to save changes?". This is probably only a workable solution if there's a limited number of forms such as this one. Also untested. And inappropriate for production. Googled a half hour with no luck, either.

 

[RBerke]

Thanks for your efforts. I have also googled my heart out with no luck.

My theory is that this is "working as designed". Since most Excel to Access usage is in production systems, perhaps Microsoft thought they were doing folks a favor by suppressing the "do you want to save" messages. If that was their rational, I think they made a poor choice – it would be better to default to 'do not save'.

Anyway, since I do not work for a billion dollar a year company, there is no way I will ever confirm my theory, much less get microsoft to change it.

As you stated, your ideas for workarounds will not work well in an environment where there are dozens of objects needing protection. The best I could come up with is a simple msgbox in my startupFromExcel routine.
If environ("username") = "myname" then
Msgbox "Be careful, setwarnings has been disabled – see mrexcel message board for details"
End if

I could even make it conditional code so that it disappears from the final compilation.

thanks again

 

[Norie]

Why are you doing things in this way?

Why do you need to open an instance of Access whenever you want to access data in a database?

There are plenty of ways to export/import data between Access/Excel.

In fact why are you using both applications in the first place?

Couldn't everything be done in one of them?

 

[RBerke]

Why two application? It is partly historical - we are tying together two applications one Excel based and another in Access.

For instance, the customer contact database and invoice system is already in Access and has a nice user interface. Why would I want to replicate that inside of Excel when the Access system is already working and the user like it?

But, even if I was starting over I would probably not use a single platform. There are clearly somethings that are better in Excel and somethings that are better in Access.

For instance, I often download Access data into Excel for further ad hoc analysis because Excel is better at charts pivot tables etc. Then, when the Excel analysis reveals that a customer's billing parameters needs to be changed, it seems desirable (and easy) to have a single click transfer into the Access system.

And, in reality all those links work fine. The subject of my post is just an annoyance to the developer (me). It does not affect the users at all.

 

[RoryA]

If you want to leave it open for the user to work with and save changes, then try adding:

accapp.UserControl = True

to the end.

 

[Norie]

I'm not questioning your approach, if it works it works.

But it just seems a little bit overkill/overcomplicated doing things the way you are.

 

[RBerke]

Rory comes through again. I put in accapp.usercontrol = true and my application now behaves properly.

Thanks a ton, I was just about to give up.

Bob

Private Sub CommandButton1_Click()

    lPath = "c:\db1.mdb"
again:
' 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 any leftover access instances
'
    On Error Resume Next
    Set accapp = GetObject(, "access.application")
    If Err = 0 Then
        accapp.Quit
        Set accapp = Nothing
        GoTo again
    End If
    On Error GoTo 0
' The above process killing code will eventually 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