Closing a database and Access at the same time (1 Viewer)

[The_Doc_Man]

I'm getting odd behavior when closing all forms and making Access close, too.

Scenario: Access 2003 DB (soon to be converted to Ac2007, but not soon enough - we have configuration management here that slows down everything we do.) Split FE/BE files, both .MDB format at this time, but running with Ac2007 installed on the system and running the .MDB files.

In the database, I have the MasterControlForm (MCF) as a startup form. It includes the usual gang of events. The startup form tests for certain data safety conditions. If it finds one of the dangerous conditions, it disallows further actions that could compromise data integrity. So the real goal I was testing is to shut down the Access session when bad conditions are found.

The MCF Form_Load code includes this fragment:

        DoCmd.NavigateTo "acNavigationCategoryObjectType"
        DoCmd.RunCommand acCmdWindowHide
        DoCmd.ShowToolbar "Ribbon", acToolbarNo
        bFoundRef = ChangeProperty("AllowSpecialKeys", dbBoolean, False)

The ChangeProperty routine sets the database propery of the given name to the given value. (Found the routine by searching the forums and following links.) This is the property you can touch when in Ac2007 you click the Office button >> Access Options >> "Current Database." We know that call works, see later in the description.

The desired result of this first snippet is that you should not see the navigation pane or ribbon and cannot use special keys. That works correctly. I don't undo or reverse those actions in the production copy, there should be no way for you to see the structure of the database.

By design, when one of the "nasty" conditions is detected, the Form_Load code does this:

        On Error Resume Next
        DoCmd.Close acForm, Me.Name, acSaveNo
        On Error GoTo 0

Via breakpoints, I saw that after detecting a bad condition, Access actually executed the DoCmd.Close of the form. Access fires the Form_Unload event and the Form_Close event. (No Deactivate event was defined.) Form_Unload in this case doesn't do much, just audit-logs that the database is being closed. It never disallows the Unload. I.e. the Cancel parameter of the Unload event is always 0 (False).

In the Form_Close of the MCF, I take extra care to assure that no recordsets and no subforms are still open. I've used the debugger to set a breakpoint in the Form_Close event so I can watch the Locals window. The remaining open recordset gets closed (confirmed via breakpoint) before running the code snippet below, which is at the very bottom of the Form_Close event code. This next sequence is where my insanity starts.

        DoEvents
        Application.Quit acQuitSaveNone

Everything I've found on-line says that after you do a QUIT command, you don't execute the next line of code. What actually happens is that Access gets to the code above (verified by single-step.) So it executes the Quit method.

The application quits but Access stays open! The FE's .LDB file stays open. Task Manager confirms that MSACCESS.EXE stays open. I have used all sorts of variants here. Application.Quit, Access.Quit, DoCmd.Quit, DoCmd.RunCommand acCmdExit, ... All of the obvious ones and more than a few less obvious ones. Same results each time.

The ChangeProperty call shown earlier DOES prevent use of F11 when the application database closes leaving the Access window open. If I didn't use that call, F11 would bring up the Navigation pane for the database that should have closed fully but did not. Because the database is still open AND LOCKED, users would be impacted if they tried to correct the condition and re-open the database.

Under the circumstances of the test, all timers have stopped and been reset. All other forms either never opened or were closed before getting to that point. The DoEvents call should have allowed all of the sub-forms to settle out, too.

My co-worker and I have researched this for most of the day, hitting this forum, the UtterAccess forum, and several MS Knowledge Base articles, but so far we have not figured this one out. We cannot find anything obvious that is still open that would hold the database in an unclosable state.

Any suggestions out there?

 

[Banana]

Have you checked CurrentDb.Recordsets? What about DBEngine.Databases collection? I've had a case where I can't close an automated Access instance until I close all recordsets/databases via the collections.

Is there a object reference to the Access.Application object by somewhere else? (example: I know of a .NET manager that may start up Access but when it's not shut down properly, Access refuses to close down because it thinks it's being referenced by (now-dead) .NET manager. This can happen with something more benign - VBScript for example)

That's two major thing I can think of right now.

 

[The_Doc_Man]

I'll see about the "recordsets" collection later today. That might take some enumeration code that I wasn't planning on running in that context. Didn't think about the .Databases collection, but in a split DB, that's an interesting point. On the other hand, wouldn't ALL non-local databases (I'm really thinking of the BE database) close when I close the last recordset to any such DB and allow a DoEvents to do its thing?

 

[Banana]

In theory, yes, that's correct.

However, as mentioned, I had a case where I was automated Access and I couldn't get it to close because it had a Database open which then messes up the automation because I can't move/copy the file. When I added code like this:

Do Until Acc.Databases.Count = 0
  Acc.Databases(0).Close
Loop

it then worked reliably and closed Access consistently.

 

[The_Doc_Man]

As a follow-up: I commented out the code that disables the "Allow Special Keys" option on Form_Load. I set breakpoints in the code again, but now I can't take them! I think I know why, but what vexes me is that I left a code window open and it allowed me to open the immediate window. I didn't think that was possible unless a database was open. (And in fact that may be the case...)

Anyway, there are no recordsets open at the time, and the workspace says one database is open - my Front End. How it can still be open when I've done a DoCmd.Quit, I'm not sure. I'm going to try the 'Do Until' loop per Banana's suggestion.

 

[The_Doc_Man]

Well, THAT was fun. It hung up Access. After 30 seconds, I told it to close Access. I got a "not responding" so I told it "End Now." Access tried to send an error report (which I blocked), and then opened the FE file again! Clever of it to re-open that which I really wanted closed.

There are times when I really HATE Access and would willingly find a hole in which to bury it DESPITE its many redeeming qualities.

 

[The_Doc_Man]

I'm taking out that loop to close the databases because as long as the code is running, it cannot close itself. Infinite loop time.

 

[Banana]

Sorry for not having made it explicit that we of course couldn't close our current database - the code I gave was from automating the Access from outside which is a different horse. I guess it would work if it was Count = 1 though there's the off chance that you have two Database opened and they're same file (yes, I've seen that) and there's no way to tell which is the CurrentDb and which is not. Fun!

I'm not sure I follow the part about code windows still open - it's always been the case that if you can open Access (whether a database is open or not), you still can go to VBA editor and do some commands. Not all commands but some.

 

[The_Doc_Man]

This gets curiouser and curiouser, and yes I'm beginning to feel like the Mad Hatter!

Point of clarification: This is a case of Access opened by direct launch of the .MDB file, not an automation case where something else opens Access for this database.

A couple of articles pointed to file corruption being a problem, one that isn't fixed by a simple compact + repair. So I did that painful step of creating a new database and importing everything to it. However, ... no joy. I still get the same behavior. So I guess it must not have been file corruption.

I put this code snippet in the startup form's Form_Unload(cancel as Integer) event to track what was open.

    If bBadOpen = True Then
        For Each dbX In Workspaces(0).Databases
            AuditEvent evtInternal, lMySA, "Database " & dbX.Name & " is still open."
            For Each rsX In dbX.Recordsets   
                AuditEvent evtInternal, lMySA, "Recordset " & rsX.Name & " is still open."
            Next rsX   
        Next dbX
     
        For Each fmX In Forms
            AuditEvent evtInternal, lMySA, "Form " & fmX.Name & " is still open."
        Next fmX
    End If

Trust me on the variables, they ARE the appropriate object types for the actions inside the loops. The "bBadOpen" flag tells me that I had one of the abort conditions. I wanted to document what was open before I let go of the event when this oddball case crops up.

In the first experiment, the audit logs say that I have one form open (the startup form, no DUH there), one recordset open (through a query linked to a table in the back end file), and TWO databases open - and they are the same file, the front end.

I have done a code search through the entire project to see if I have ever explicitly opened a database object through code. I use the CurrentDB "object" a lot, but I do not open any explicitly declared database object that isn't immediately closed in the same procedure call.

My symptom remains that I cannot close my Access application cleanly. I can get the database to close, but Access stays open. OR I can get Access to appear to close, but Task Manager says it is open and the .LDB file doesn't go away until I kill the running copy of Access and delete the .LDB by hand. The difference is whether I use DoCmd.Quit or Application.Quit. I also tried to just let the form close normally, but that also leaves Access visibly open.

I experimented more. I tried closing the recordset before I ran the above code sequence. Lo and behold, the second instance of the FE file went away and the recordset was confirmed as closed. But the closure problem persists.

As of right now, I can either make Access appear to close (but the .LDB is still open and Task Manager says it is still there) or close the application and leave Access visibly open. Neither one of those is acceptable behavior and I'm damned if I know what is causing this.

 

[ChrisO]

Well, this might seem obvious…

Have you tried making a copy and stripping out all the stuff that should have nothing to do with the problem?
All the other Forms, Reports, Modules, Queries then delete the linked Tables.

In other words, strip it till it works and then look at the last thing removed.

Chris.

 

[ChrisO]

Another thing to try…

Is Access broken or is it your database that’s broken?
Make a new database with only one startup Form and tell it to Quit; does it?

Chris.

 

[Banana]

Just to confirm, Doc_Man, when you found that you had two databases opened in the DBEngine.Databases, did you try to close one of either? What happens then? Closing recordsets may not be enough.

 

[The_Doc_Man]

I am beginning to see the light on this one.

You can call me lazy but what I was doing was building display elements in the Form_Load routine and testing some of what I found... killing two birds with one stone, in a sense. But... let's jump to theory.

When you open a form, you get these events in order:

Open >> Load >> Resize >> Activate >> Current

When you close a form, you get these events in order:

Unload >> Deactivate >> Close

Of these, only the Open and Unload can be canceled. Once you let the Open event get by without a Cancel, I think you cannot kill the app until after the Current event has fired and you hit the Exit Sub.

I think this is true because I had one of those "sanity" tests in the Form_Open event. The others were in the Form_Load event. So I forced a violation for the test that was in the Form_Open event, which does two things: An Application.Quit AND a Cancel = -1 (not necessarily in that order.)

The behavior resulting from this test was correct in that the application shut down, the form never opened, AND Access shut down afterwards, too. The .LDB file went away. Task Manager confirmed that there was no copy of MS Access hanging around behind the scenes.

I had a functioning EXIT command button that did some event logging and then did the Application.Quit - and that worked correctly in the Exit button's OnClick event. But the same exact command failed in the Form_Load. That was part of why I was going bonkers - the code worked in one place but not in another. So this experiment in a third place is what finally put me on the right track.

It is beginning to smell like this: If you don't cancel the Open event, then you are essentially committed to opening all the way, hitting all events through the Form_Current event. Any attempt to kill the app or form in the intervening events after the Open event is doomed to failure with really odd behavior.

I will have to restructure my sanity tests to isolate them so that they can be called safely from the Form_Open event and provide feedback to the Cancel parameter that will allow me to kill off the offending users who trip the sanity traps. I can't get away with doing them in the Load event. What I will have to do is to pass the results of some of the sanity tests to a common area so that I don't waste what I found out from the Open event code. Talk about a trip through the rabbit hole...!

 

[The_Doc_Man]

Have you tried making a copy and stripping out all the stuff that should have nothing to do with the problem?

ChrisO, a huge part of the problem was that this was the startup form. It was not possible to open any other forms until this form was up. So no forms were open. I verified that by an enumeration of the Forms collection. Closing the recordset worked because somehow it seems that the recordset is an implied open of the database. When I closed the recordset, the second member the databases collection went away. I'll bet the problem was that the query is a FE query pointing to a BE table, so at first blush it appears that the FE is open.

If I have too many more learning experiences like this one, my next thread will be posted from a specially decorated room with lots of padding. Not only that, I'll have trouble typing through the straight jacket.

 

[ChrisO]

I can’t reproduce the problem in XP A2K3 so it might be a Win7 A2K3 problem.

If you can’t reproduce it in XP A2K3 then it should simply be with Win7.

If it is Win7 then there might be a Service Pack which fixes it.

Chris.

 

[The_Doc_Man]

It is WinXP SP3 running Ac2007 on an Ac2003-format database, and because of the site security I couldn't apply a service pack to anything if I wanted. But the odds are that if a service pack has been identified, it either has already been or soon will be pushed to my system via Radia. I don't know when we will upgrade to Ac2010 or Win7, though both are likely. In our environment, the contractors are usually the last to know anything - unless they screwed up, in which case the anatomical rearrangement with the post-hole auger occurs very quickly.

To be fair, the site isn't as bad as I make it sound. It just has its bad moments. However, as close as I am to retirement, I've learned perspective.

 

[ChrisO]

My mistake, I misread your first post in this thread.

Still would nice to know if the fault is reproducible across versions of Access.

Good luck,
Chris.

 

[The_Doc_Man]

Just so this will be on record for future readers looking for answers:

I cannot say 100% that I'm right because I don't have the Access.EXE source code in front of me, not that I read "C#" that well anyway. But this is what I've found by painful experiment.

The original problem was how to shut down Access and my application when a serious error is detected in the initial conditions. I didn't want someone to be able to continue in any of the "abort startup" cases because of the possibility that they were going to be able to compromise database integrity.

When I take my Form_Load code that was doing my closure tests, move the open/close portion to Form_Open, and use this sequence for the "don't allow access" cases, it works as it should. Access closes. The application files close. The lock file vanishes.

    Cancel = -1
    Application.Quit acQuitSaveNone

Everything seems to be better if you restrict this kind of test to the Form_Open event. If you do the same thing from the Form_Load event or later in the event-chain then it fails miserably. In the end analysis, it was poor execution on my part to try to do the tests and some display changes in the Form_Load routine. You have to keep the functions separate. To which you might say, "Well, DUH, of course." But sometimes that isn't so obvious.

If you want to close the form before it really opens, you HAVE to do it in the Form_Open routine. If you defer any open/close decisions to later events in the chain Open >> Load >> Resize >> Activate >> Current, you are doomed to having the form open anyway, plus you get all sorts of other ugly behavior.

If you want to do some control tailoring, you have to wait until at least the Form_Load routine because stuff isn't fully available in Form_Open. So you are forced by issues of "what is available when" to keep the two functions separate. In Form_Open, you can do go/no-go testing but no tailoring. In Form_Load, you can do tailoring but you are going to open the form, ready or not. The price I paid for this transgression was four days of tearing my hair out and thinking I had gone crazy. I think I might actually remember this lesson the next time this kind of problem crops up.

 

[Banana]

FWIW -

I do have kind of similar problem where I try to do too much in the BeforeUpdate/AfterUpdate sequence. The mantra ought to be "validate in BeforeUpdate, modify in AfterUpdate", right? Well, sometime, I've forgotten to observe this rule and try to mush both thing in a single go. As you said, on the paper, it looks "DUH obvious" but when in thick of it, it sounded reasonable. For example, I'm thinking something like "this values is valid but I need it formatted the other way so let's format it right now" (no, you get an error where Access blocks you from making such changes as that would potentially trigger an infinite loop).

Anyway, glad you're sorted.