Access is still listed in Task Manager after closing

Question

re: Access 2003 / Win 7 Pro

Once my Access app is closed and I execute a DoCmd.Quit on the main forms' exit button, "msaccess.exe" still shows up as active in Windows Task Manager.  

I thought DoCmd.Quit closes the app and shuts down Access completly.  Any remedies via VBA that I can include in the app to ensure that it gets flushed from task manager when the app and MSAccess shut down?

---

RLN

Answer 1

Try Application.Quit or just plain Quit (same thing). The Docmd.Quit is for backwards compatability and should work, but maybe the other will be cleaner.

---

Bill Mosca
http://www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 2

using "Application.Quit" did not solve the problem.

I am launching a login form first, a data form second.   There is an 'exit' button on the data form that closes the app and shuts down Access.  

---

RLN

Answer 3

There is an example of how to do it here:

http://www.xtremedotnettalk.com/showthread.php?t=79163

Post back if you have additional questions.

---

Ryan Shuell

Answer 4

I checked the URL you listed.  In there:  what is "GC.Collect()" ?   I'm not sure what he means there.   Also, we are not using .Net in this application.  If I was reading the code correctly in the link (at xtremedotnettalk.com) they are suggesting that a .Net app launch my MS Access application, and the .Net app takes care of destroying the instance in memory of MSAccess.  Was I reading everything correctly?

So, the code author is launching the Access app externally to get it to close correctly.  

Why won't Application.Quit work on a "Quit" button inside of an Access app?

---

RLN

Answer 5

Hi RLN60

RLN60 wrote:

Once my Access app is closed and I execute a DoCmd.Quit on the main
forms' exit button, "msaccess.exe" still shows up as active in Windows
Task Manager.

Didn't see this bug a long time anymore. It was very common in A97. It is described here. Not sure if this happens to you.
http://access.mvps.org/access/bugs/bugs0005.htm

Most common reason is that you don't distroy references to Jet objects that you are opening yourself.
For example:

Dim db as Database
Dim rs as Recordset
set db = Currentdb()
set rs = db.openrecordset(....., ...)

if you now forget to write
rs.close
set rs = nothing
set db = nothing
then this can (but not reproduceable) happen.

Another reason can be that you forgot to close a text file by code. It's important that all files are closed by the Close command.

Finally I remember that this bug sometimes occured when you used . insted of ! to reference and also when you referenced to a checkbox in a form without explicitly referencing it's .value property.

But as I mentioned I didn't see these bugs for years, maybe you found a new one.

Here some links to knowledge base articles that are referencing to these bugs
http://support.microsoft.com/?kbid=164455
http://support.microsoft.com/?kbid=190074
http://support.microsoft.com/?kbid=209847
http://support.microsoft.com/?kbid=246953

Last but not least: When did you last /decompile your MDB?

Henry

Answer 6

Simple question (Assuming you are using ADO w/in your code):"Do you have a recordset or connection still open?"

I've always read (never had it happen myself) that leaving a recordset or connection open--especially if it is locked in a query or filter mode--could cause this sort of behavior. Not sure if this is the culprit, but worth looking into. 

Hope This Helps--Leo

Answer 7

Leo,

I try and exercise all due diligence to close a recordset as quickly as I can after using it.  (for example, putting rst fields into local variables, closing the recordset then working with the variables from there on.....

At the time you issue an "Application.Quit" statement, (or just prior) is there an "Across-the-board" way to see if there are any recordsets open, and if so issue a rst.close and set rst=Nothing statements then fire the "Application.Quit" line?

---

RLN

Answer 8

oops...sorry.  I forgot to answer your inquiry as to ADO.....

I am using DAO recordsets as those recordsets are going against small local Access tables with not much data in them.

---

RLN

Answer 9

Leo,

I try and exercise all due diligence to close a recordset as quickly as I can after using it.  (for example, putting rst fields into local variables, closing the recordset then working with the variables from there on.....

At the time you issue an "Application.Quit" statement, (or just prior) is there an "Across-the-board" way to see if there are any recordsets open, and if so issue a rst.close and set rst=Nothing statements then fire the "Application.Quit" line?

---

RLN

There are no "Across the board" methods for that. If you use global object variables you can go through them and set them all to nothing. Routine-limited objects should just go out of scope.

One thing I do is to make sure all my local objects are set to Nothing in my exit area of error traps such as here:

exit_PROC:
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
err_PROC:
    'blah
    Resume exit_PROC
End Sub

That way even if a routine errors out the objects get closed.

Maybe this is all a matter of timing. Is your Quit command in the data form's close event? it should be the last line in it. That login form isn't still open, right?

---

Bill Mosca
http://www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 10

t rs = Nothing
    Set db = Nothing
    Exit Sub
err_PROC:
    'blah
    Resume exit_PROC
End Sub

When closing a recordset, (DAO or ADO), can I just say "set rst = nothing" or do I have to first say, "rst.close" THEN "set rst= nothing"?   Which is considered proper code?  (fwiw,....I've always done "rst.close" then set rst = nothing")

>>Is your Quit command in the data form's close event?<<

no, it is in a command button on the form.

>>That login form isn't still open, right?<< 

yes, the login form is still open.      I guess I could do something like this to interrogate if the form is open prior to running the "Application.Quit" line:

Function FormIsLoaded(ByVal strFormName As String) As Integer
    If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> 0 Then
         If Forms(strFormName).CurrentView <> 0 Then
            FormIsLoaded = True
         End If
    End If
End Function

Is it better to just to issue a close to my Contract form, then in the Contract form's close event, do Application.quit at that point rather than in the command button on the form?

---

RLN

Answer 11

Setting it to nothing will destroy it. There is no need to close it first. What is SUPPOSED to happen is that any object within a scope is destroyed when the routine or container is destroyed. In the days of Access 97 it was common practice to make sure you close and set recordsets to nothing, but the more I read on this topic leads me to believe we are all just being paranoid.

And that leads me to believe your application is hanging for some other reason. What happens if you comment out the Quit line and close the form an dthen manually close Access using the X button?

---

Bill Mosca
http://www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 12

Per your counsel Bill, I tihkn I will take out the lines "rst.close" and leave the "set rst = Nothing" so that will do the same thing.

>>What happens if you comment out the Quit line and close the form an dthen manually close Access using the X button?<<

Access is still in memory.   (??)  not sure what is goin on there.   We are thinking of uninstalling Access and re-installing it.  (Good idea...bad idea?)

---

RLN

Answer 13

 >>We are thinking of uninstalling Access and re-installing it.  (Good idea...bad idea?)

Sounds like that might be a good idea. When you uninstall it use a robust uninstaller like Revo Uninstaller. That will uninstall, remove all leftover folders and reg keys. I've found it works very thoroughly. Just using the control panel remover will leave behind all kinds of garbage that could make re-installing fruitless.

Once you uninstall, reboot and re-install be sure to run the Windows Updater.

You're not running other Office products of a later version, are you? I run into IE 8 hanging in the taskmanager from time to time due to a DLL conflict running Office 2007 (all accest Access) and Access 2003 on Win XP. If the problem persists and you are running more than one version of Office products, now would be the time to settle for just one. Or develop 2003 on another computer.

---

Bill Mosca
http://www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 14

Per your counsel Bill, I tihkn I will take out the lines "rst.close" and leave the "set rst = Nothing" so that will do the same thing.

>>What happens if you comment out the Quit line and close the form an dthen manually close Access using the X button?<<

Access is still in memory.   (??)  not sure what is goin on there.   We are thinking of uninstalling Access and re-installing it.  (Good idea...bad idea?)

---

RLN

Hi RLN,

I do not believe your problem is related to "rst.close" or "set rst = Nothing".
I all my applications I never use "set rst = Nothing", and sometimes I use "rst.close" but mostly not. But, "rst"is always a local variable.

I have met your problem a couple of times using A2003 on WindowsXP. It occurred after "heavy" development work, and the last time that it happened was a few years ago. Now I am using A2003 on Windows7, without that problem.

It is my feeling that the problem is the result of improper program logic, e.g. quitting a loop and do things, while after normal finishing the loop other things are done. But I can not remember the real situation. In any case, I never did a re-install of Access to get rid of the problem, but it "faded away finally".

Imb.

Answer 15

Something else you may want to look at before going to the trouble of reinstalling Access.

When in Task Manager go to the Performance Tab and check CPU usage. Is there a lot of CPU usage ~50% maybe?

The following type of code can cause Access to appear to close:-

Private Sub Form_Close()
    Dim X As Long
    
    On Error GoTo ErrorHandler
    
    X = 1 / 0
    
ExitProcedure:
    Exit Sub
    
ErrorHandler:
    Resume

End Sub

But it is too busy handling the error to shut down.

Chris.

---

Brisbane Australia, GMT+10, Access 2003

Answer 16

Good deal, thank you Chris.   

---

RLN

Answer 17

>>It is my feeling that the problem is the result of improper program logic<<

I would agree.    I need to take a look at the code to see what is going on there.

Is it possible at the time you issue an "Application.Quit" statement to see what rst's are still opened, etc.?

---

RLN

Answer 18

Right now the client is running Access 2003 alongside of Office 2010--Word, Excel, Powerpoint and OneNote.

This is also true for their development environment.

---

RLN

Answer 19

Something a little more subtle…

In a Standard Module:-

Option Compare Text
Option Explicit

Public PersistentRecordset As DAO.Recordset

Behind any Form:-

Option Explicit
Option Compare Text


Private Sub Form_Open(Cancel As Integer)

    Set PersistentRecordset = CurrentDb.OpenRecordset("SELECT * FROM tblMyTable")

End Sub


Private Sub cmdShutdown_Click()

    Application.Quit            ' < This is the culprit.

End Sub


Private Sub Form_Close()

    On Error GoTo ErrorHandler
    

ExitProcedure:
    PersistentRecordset.Close   ' < This is all it requires.
    Exit Sub
    
ErrorHandler:
    Resume ExitProcedure
    
End Sub

This problem is not about the PersistentRecordset being left open, quite the contrary.
The fact is that the garbage collector has done its job and has already closed PersistentRecordset.

The garbage collector was called by Application.Quit before the Form_Close event was called.

Any Application.Quit command will call the garbage collector and it doesn’t matter if that command is on another Form.

Once the Application.Quit command has called the garbage collector all variables have been reset.
The Application.Quit command then starts a shutdown sequence.
If any Form is open then an attempt is made to close it.
If the Form has a Form_Close or Form_Unload event those events will fire.
When invoked by the Application.Quit command those events are running with PersistentRecordset which has already been closed.

So, if you are going to look for recordsets which have not been closed then save yourself some time. Look for recordsets where an attempt is made to close them and remove that attempt.

We paid good money for the garbage collector so we should use it; it works.
Trying to do the garbage collection ourselves can lead to failure.

But any such circular error, not just recordset errors, will cause Access to be too busy to close.

Chris.

---

Brisbane Australia, GMT+10, Access 2003

Answer 20

I checked all of my recordsets and I have the "set rst = Nothing" in my modules where needed so that the rst objects are properly destroyed when I'm done using them.  Aside from that, is there a utility you know of that will tell you what is still not closed that is causing Access to remain in memory?  My application is large and trying to search/test every conceivable possibility to try and find what is causing Access to remain in the task manager would be a daunting undertaking.

---

RLN

Answer 21

I checked all of my recordsets and I have the "set rst = Nothing" in my modules where needed so that the rst objects are properly destroyed when I'm done using them.  Aside from that, is there a utility you know of that will tell you what is still not closed that is causing Access to remain in memory?  My application is large and trying to search/test every conceivable possibility to try and find what is causing Access to remain in the task manager would be a daunting undertaking.

You could look for any open recordsets using code like this:

Dim d As DAO.Database
Dim r As DAO.Recordset

For Each d In Application.DBEngine.Workspaces(0).Databases
    Debug.Print d.Name
    For Each r In d.Recordsets
        Debug.Print , r.Name
    Next r
Next d

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 22

Hello Dirk,

Thank you for your code snippet here.  It looks like all of the recordsets are closed properly when I ran your code, but Access is still in memory after the app is closed.  This is makin' me nuts!   :-)        I have not had this ever happen in past development efforts.   I'm really stumped as to what to check next.   I can tell the user to launch the task manager and tell the user to close it if it exists, but it isn't prudent to tell the user to do that.

Similar to parsing through the objects in the container, is there a way to list what variables are still in RAM and their contents?  I wonder if that would help.

---

RLN

Answer 23

I haven't been following this for a while, and I didn't read through every line written here, so don't get mad if this has already been mentioned, but consider this:

http://www.btabdevelopment.com/ts/excelinstance

Also, look at this;

http://www.robvanderwoude.com/files/offver_vbs.txt

As I know, from doing the same thing, but in Excel, it MUST be done like this:

objAccess.Quit
Set objAccess = Nothing

Or, maybe it is like this:

Set objAccess = Nothing
objAccess.Quit

I forget how it works; try both methods.

---

Ryan Shuell

Answer 24

Hello Dirk,

Thank you for your code snippet here.  It looks like all of the recordsets are closed properly when I ran your code, but Access is still in memory after the app is closed.  This is makin' me nuts!   :-)        I have not had this ever happen in past development efforts.   I'm really stumped as to what to check next.   I can tell the user to launch the task manager and tell the user to close it if it exists, but it isn't prudent to tell the user to do that.

Similar to parsing through the objects in the container, is there a way to list what variables are still in RAM and their contents? 

There may be a very low-level way, beyond my knowledge, but I don't know of any way that is readily accessible.

Here's one thing to look at, if you haven't already.  Check through your code for all module-level or Static object variables.  I'm not talking about regular data types such as Long, Boolean, etc., but rather object types such as Database, Recordset, Application, Collection, and so on.  And I'm only interested in the object variables if they are either (a) declared at the module level, rather than inside a procedure, or (b) declared with the Static attribute.  Such objects are suspect in a case like this. Make sure that there is always code executed to close (or Quit for application types) and destroy (Set to Nothing) these variables, no matter what code path is followed through your application.

One common instance of such a variable is a database or recordset variable used to maintain an open connection to the back-end database, for performance reasons.  Make sure that this connection is closed when you exit your application.

Another thing to check through is, what is the minimum path through your application to cause the phenomenon?  If you open the application, do nothing, and then close it, do you still get the Access instance remaining in task manager? If so, then it must be something that happens automatically at startup, so you can trace through that code step by step to see at what point the problem first manifests itself.  Set a breakpoint at the first line of code that could run, then exit the database -- does the bad thing happen? Set another, later breakpoint, and exit there -- does the bad thing happen? And so on.

Also, if you are automating any other application, such as Excel or Word, does the phenomenon occur if you totally bypass that?

One more thing that occurs to me is the possibility of VB project corruption.  You mentioned something about a heterogeneous development environment.  Does this occur in a database that has been maintained both in Access 2010 and an earlier version?  If so, see if a decompile/compact/recompile process under only one Access version makes the problem go away.  That's a long shot, but worth trying.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 25

Dirk, 

First let me commend you for an exhaustive response.  WOW!    Thank you so much!   :-)    I appreciate your volume of thoroughness here. 

The application is developed in Access 2003 (version determined by client site) and it runs in production on Access 2003.  I do utilize an Excel Object as a fast track to taking a very large comma delimited text file and first linking to the comma delimited text file as an external table, then while linked, load the data into a temporary Excel object (which splits the fields out in one swift move, then manipulating the individual fields then writing the scrubbed data to a temp table.  At that point in the routine I close the Excel object completly and destroy it.  That is all at the module level so scope there is local.

Upon further investigation the Access executable only remains in memory when the following minimal path  sequence is performed: Start up the app, select contract#1 and load it, close Contract #1 (doing nothing to its data), returning to the contract list form, selecting Contract #2, close Contract #2 (doing nothing to its data as well), then close the application and close Access.

Soooo.....I have more investigation work to do on the application to locate the culprit.   I think you're right though when you mentioned the connection to the back end database (in this case, it is SQL Server).  I don't know that it is disconnected upon app closure.  If I am using an ODBC data source, it there a "best practice" standard you would recommend for closing them effectively?

(A sidenote:  I realized I have posted many threads, and apologize for not being able to respond to others' posts.   I hope to be able to do that to return the favor, but do not want to steer someone else the wrong direction with the wrong methods.  Since I am not certified in Access, I personally don't exactly feel qualified to post answers.  Perhaps I can get certified someday.)

---

RLN

Answer 26

Hello Ryan,

>>so don't get mad if this has already been mentioned<<

no worries there.....sometimes reiteration is a good reminder.

I am closing my Excel session this way:

--objWkb.Close
--objXL.Quit
--Set objXL = Nothing

---

RLN

Answer 27

Hi RLN60

RLN60 wrote:

Thank you for your code snippet here. It looks like all of the recordsets
are closed properly when I ran your code, but Access is still in memory
after the app is closed. This is makin' me nuts! :-) I have not had this
ever happen in past development efforts. I'm really stumped as to what to
check next. I can tell the user to launch the task manager and tell the
user to close it if it exists, but it isn't prudent to tell the user to
do that.

I'd have been surprised if this code would have worked in your case. If it's the bug that is described by MichKa in the already mentioned link http://access.mvps.org/access/bugs/bugs0005.htm then it's a bug in the reference count of a DLL. The database object is distroyed but the reference count to the DLL isn't decremented so Access believes there is still a database open even you can't see/find it. It believes it because the reference count is > 0 and therefore won't close the connection and distroy itself. It waits until the reference count becomes 0 what will never happen.

You really have to find all locations where you open a Jet object and distroy it manually. It's a lot of work but I can't see any other possibility to get rid of it.

It may be caused by your environment. Please also check the Jet/Dao versions and the versions of the referenced software.

It BTW may also be caused by another reference outside of Access, for example a reference to Excel that you don't close correctly.
What references do you have in your application (VBA references). Did you consider to switch everything except the references Access really needs to late binding? This may solve your problem and it's not so difficult to achieve.

Henry

Answer 28

In Task Manager, what is the CPU loading at the time Access gets stuck?

Chris.

---

Brisbane Australia, GMT+10, Access 2003, Public at SkyDrive

Answer 29

I've done lots of decompiles.   

Per this link:  http://access.mvps.org/access/bugs/bugs0005.htm

Is there a code sample to support the link above?  I dimmed "Dim dbe as New PrivDBEngine" and then attempted to try and simply list the columns in a table like this but it did not work:  (works with regular DAO but not with the dim of the new "PrivDBEngine")

   Dim db As New PrivDBEngine
   '....Dim db As DAO.Database
   Dim tdf As DAO.TableDef
   Dim fld As DAO.Field
  
   Set db = CurrentDb()
   Set tdf = db.TableDefs(strTableName)
   Dim strMsg As String
   
   For Each fld In tdf.Fields
      'Debug.Print FieldTypeName(fld),
      'Debug.Print fld.Size,
      'Debug.Print GetDescrip(fld)
   Next

  
TableInfoExit:
   Set db = Nothing
   Exit Sub

---

RLN

Answer 30

I've done lots of decompiles.   

Per this link:  http://access.mvps.org/access/bugs/bugs0005.htm

Is there a code sample to support the link above?  I dimmed "Dim dbe as New PrivDBEngine" and then attempted to try and simply list the columns in a table like this but it did not work:  (works with regular DAO but not with the dim of the new "PrivDBEngine")

   Dim db As New PrivDBEngine
   '....Dim db As DAO.Database
   Dim tdf As DAO.TableDef
   Dim fld As DAO.Field
  
   Set db = CurrentDb()
   Set tdf = db.TableDefs(strTableName)
   Dim strMsg As String
   
   For Each fld In tdf.Fields
      'Debug.Print FieldTypeName(fld),
      'Debug.Print fld.Size,
      'Debug.Print GetDescrip(fld)
   Next

  
TableInfoExit:
   Set db = Nothing
   Exit Sub

Now you're getting a little far afield in trying to solve your problem. I don't think this is the best approach to figuring out what's wrong; a better approach is to step progressively through the code until you find the specific operation that results in the application being unable to close fully.

However, to answer your question, the PrivDBEngine object is not a Database object, it's a whole new database engine. So you need to have a separate variable for that database engine, and then open the database inside that database engine. Something like this:

    Dim dbe As New PrivDBEngine
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
   
    Set db = dbe.OpenDatabase(CurrentDb.Name)
    Set tdf = db.TableDefs(strTableName)

    For Each fld In tdf.Fields
        Debug.Print FieldTypeName(fld), fld.Size, GetDescrip(fld)
    Next fld

    Set tdf = Nothing
    db.Close
    Set db = Nothing
    Set dbe = Nothing

However, I really don't think this is the right approach to solving your problem.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 31

After rereading this thread I think this is an oversight on my part as well.

No one has asked; if the application was run with error handling turned off, would it crash and display an error message?

From any code module:
Tools>Options>General>Error Handling and select 'Break on All Errors'.

In other words, don't look for the error, let Access tell you what it is.

Chris.

---

Brisbane Australia, GMT+10, Access 2003, Public at SkyDrive

Answer 32

Dirk,

Thank you for this reply.  First let me apologize for being away from the forum as I have been on other projects.

I still try and investigate this as it still happens occasionally.    There is probably something not getting explicitly closed, but I will continue looking at it.

I have written a routine that looks for open objects when the quit command is activated:

Public Sub CloseAllDBObjects()
''======================================================
'' Module:    CloseAllDBObjects
'' Descr:     Closes any open DB Container Objects
''            when the application closes.
'' Called by: frmContractsImport--cmdClose_Click()
'' Calls:     none.
'' Parms:     none.
'' Returns:   nothing.
'' Comments:  This routine runs just prior to the Application.Quit command.
''
'' Changes----------------------------------------------
'' Date     Programmer    Change
'' ----     ----------    ------
'' <date>   <name>        <change>
''
'' --RLN-2012
''=======================================================

On Error GoTo Err1

    Dim aob As AccessObject
    Dim strMsgHdr As String
    Dim strMsgDtl As String
    Dim intCount As Integer
    
    With CurrentData
        strMsgHdr = "Tables Closed: "
        intCount = 0
        For Each aob In .AllTables
            If aob.IsLoaded Then
                intCount = intCount + 1
                DoCmd.Close acTable, aob.Name, acSaveYes
                strMsgDtl = strMsgDtl & strMsgHdr & " " & intCount & " -- " & aob.Name & vbNewLine
            End If
        Next aob
   
   
        strMsgHdr = "Queries Closed: "
        intCount = 0
        For Each aob In .AllQueries
            If aob.IsLoaded Then
                intCount = intCount + 1
                DoCmd.Close acQuery, aob.Name, acSaveYes
                strMsgDtl = strMsgDtl & strMsgHdr & " " & intCount & " -- " & aob.Name & vbNewLine
            End If
        Next aob
     End With
    '---------------------------------------------------------
   
   
   
    '---------------------------------------------------------
     With CurrentProject
        strMsgHdr = "Forms Closed: "
        intCount = 0
        For Each aob In .AllForms
            If aob.IsLoaded Then
                intCount = intCount + 1
                DoCmd.Close acForm, aob.Name, acSaveYes
                strMsgDtl = strMsgDtl & strMsgHdr & " " & intCount & " -- " & aob.Name & vbNewLine
            End If
        Next aob
   
        strMsgHdr = "Reports Closed: "
        intCount = 0
        For Each aob In .AllReports
            If aob.IsLoaded Then
                intCount = intCount + 1
                DoCmd.Close acReport, aob.Name, acSaveYes
                strMsgDtl = strMsgDtl & strMsgHdr & " " & intCount & " -- " & aob.Name & vbNewLine
            End If
        Next aob
   
        strMsgHdr = "Pages Closed: "
        intCount = 0
        For Each aob In .AllDataAccessPages
            If aob.IsLoaded Then
                intCount = intCount + 1
                DoCmd.Close acDataAccessPage, aob.Name, acSaveYes
                strMsgDtl = strMsgDtl & strMsgHdr & " " & intCount & " -- " & aob.Name & vbNewLine
            End If
        Next aob
   
        strMsgHdr = "Macros Closed: "
        intCount = 0
        For Each aob In .AllMacros
            If aob.IsLoaded Then
                intCount = intCount + 1
                DoCmd.Close acMacro, aob.Name, acSaveYes
                strMsgDtl = strMsgDtl & strMsgHdr & " " & intCount & " -- " & aob.Name & vbNewLine
            End If
        Next aob
   
        strMsgHdr = "Modules Closed: "
        intCount = 0
        For Each aob In .AllModules
            If aob.IsLoaded Then
                intCount = intCount + 1
                DoCmd.Close acModule, aob.Name, acSaveYes
                strMsgDtl = strMsgDtl & strMsgHdr & " " & intCount & " -- " & aob.Name & vbNewLine
            End If
        Next aob
    
     End With
    '---------------------------------------------------------
     
    'check for any open DAO recordsets before quitting...
    Dim d As DAO.Database
    Dim rDAO As DAO.Recordset
   
    strMsgHdr = "DAO Recordsets Closed: "
    For Each d In Application.DBEngine.Workspaces(0).Databases
        Debug.Print d.Name
        For Each rDAO In d.Recordsets
            intCount = intCount + 1
            strMsgDtl = strMsgDtl & strMsgHdr & " " & intCount & " -- " & d.Name & vbNewLine
            Debug.Print , rDAO.Name
        Next rDAO
    Next d
    '---------------------------------------------------------
         
         
         
    'check for any open ADO recordsets before quitting...
    Dim rADO As ADODB.Recordset
   
    strMsgHdr = "ADO Recordsets Closed: "
    For Each d In Application.DBEngine.Workspaces(0).Databases
        Debug.Print d.Name
        For Each rADO In d.Recordsets
            intCount = intCount + 1
            strMsgDtl = strMsgDtl & strMsgHdr & " " & intCount & " -- " & d.Name & vbNewLine
            Debug.Print , rADO.Properties
           
        Next rADO
    Next d
    '---------------------------------------------------------
          
          
      If Len(strMsgDtl) > 0 Then
            'there is a message to display...
            MsgBox strMsgDtl, vbOKOnly + vbInformation, "DB Container Objects Closed--CloseAllDBObjects()"
      End If
    
Exit1:
    Exit Sub

Err1:
    MsgBox Err.Number & "--" & Err.Description, vbOKOnly + vbError, gblPgmName & "--CloseAllDBObjects()"
    Resume Exit1
    
End Sub

---

RLN

Answer 33

Hi, RLN -

I don't think this part of your code is valid:

        
    'check for any open ADO recordsets before quitting...
    Dim rADO As ADODB.Recordset
   
    strMsgHdr = "ADO Recordsets Closed: "
    For Each d In Application.DBEngine.Workspaces(0).Databases
        Debug.Print d.Name
        For Each rADO In d.Recordsets
            intCount = intCount + 1
            strMsgDtl = strMsgDtl & strMsgHdr & " " & intCount & " -- " & d.Name & vbNewLine
            Debug.Print , rADO.Properties
           
        Next rADO
    Next d

It seems to me that any recordsets owned by a DAO Database object will be DAO recordsets, not ADO records.  Therefore, if any recordsets remained open after your previous step of closing the DAO recordsets -- and maybe even if not -- you woud get a type mismatch error when executing this code.

I'm not sure whether this is the right approach to solving your problem, anyway. Any database objects that are open ought to be closed by Access anyway. You mention that the problem still occurs "occasionally".  Does that mean that you found and fixed what was causing it to happen all the time?  If so, it seems reasonable to look for other places where the same logic was used, and fix it there.  If not, I still say you need to identify what is causing the problem and attack the bug itself rather than attacking the symptoms of the bug.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 34

Hello Dirk,

I have been very busy with coding the application and lots of user testing, and have not had a chance to get back to this issue.   MSAccess.exe still remains in memory after the application is closed.

As far as I could tell I have looked through the module level database objects ("=Current DB", etc) recordsets, and those are closed and set to Nothing but I will recheck them again.  

With regard to other module level declarations: I have things like  "Dim blnXYZ as Boolean", then placing various true/false conditions in this variable throughout the module containing multiple procedures.  Alongside that, I have "Dim intXYZ as Integer" and placing integer values in this variable throughout the module.  Given these two variable examples let me ask this question (because I honestly do not know the answer)  When I am done with module level declared variables, do I  need to "Set blnXYZ to Nothing"  and "Set intXYZ = Nothing" even though they are just variables and not recordset objects or database objects?

Thanks.

---

RLN

Answer 35

I have been very busy with coding the application and lots of user testing, and have not had a chance to get back to this issue.   MSAccess.exe still remains in memory after the application is closed.

Looks like it's going to be a tough nut to crack.  I still think the problem is most likely to be in the area I suggested, but it will take extensive debugging to find it. 

With regard to other module level declarations: I have things like  "Dim blnXYZ as Boolean", then placing various true/false conditions in this variable throughout the module containing multiple procedures.  Alongside that, I have "Dim intXYZ as Integer" and placing integer values in this variable throughout the module.  Given these two variable examples let me ask this question (because I honestly do not know the answer)  When I am done with module level declared variables, do I  need to "Set blnXYZ to Nothing"  and "Set intXYZ = Nothing" even though they are just variables and not recordset objects or database objects?

No. It's completely unnecessary, and wouldn't work even if you tried it, since the pseudo-value Nothing only applies to object variables.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html