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.

 

[RBerke]

One small side affect I noticed with "accapp.usercontrol = true". When the user closes the last form, the Access database no longer automatically closes and the application object is no longer released, and the MSAccess process continues to run.

I could probably figure out a way to reinstate the automatic close behavior, but I'm not sure it is worth while because my excel application will eventually reuse previously opened Access instances and processes.

But, I am having a little trouble getting that process reuse to work. I will probably post that as a separate question when I get a chance. Unfortunately, I am tied up right now and will have to leave it for another day.

Thanks again

 

[RBerke]

Also, I discovered that I do NOT need the line that says accapp.visible = True.

That property is automatically turned on as soon as usercontrol is set to True.

 

[xenou]

Thanks Rorya, RBerke. Good to learn something new for me too.