Access 2013 Macro command "QuitAccess" not working as expected

jtflex
jtflex used Ask the Experts™
on
I have an Access 2013 small application that I plan to schedule to run at night.  I have a batch file that starts Access and runs a Macro.  That macro runs several custom functions on some tables and exports their results to csv files.  The last part of the Marco is the command "QuitAccess"  

Everything runs correctly, however, On the QuitAccess command, the database is closed but the Access application remains up and running.

I made the location of this access database a Trusted location but it still does not close the application.

Am I missing another piece to this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Issue an Application.Quit in code rather than the macro.  See if that clears it up.

  If not, then you have a hanging reference somewhere.   Make sure you are closing any objects you open and are setting object variables to nothing.  i.e.

  Dim rst As DAO.Recordset

 set rst = ....

 'Clean up.

  rst.Close
  Set rst = Nothing

Jim.

Author

Commented:
Those suggestions didn't seem to work.  Here is a sample of one of my custom functions.  The other functions are similar but point to different tables and create separate csv files:  Can you look to see if there is something in the function that is not correct?



Option Compare Database

Public Function ExportCaseManagement()


Dim MyDB As DAO.Database
 Dim rst As DAO.Recordset
 Dim intFldCtr As Integer
 
 Set MyDB = CurrentDb
 
 
 MyDB.Execute "Delete * FROM [Case Management File]" ' delete contents so an append can populate tables'
 
 MyDB.Execute "7 Case Management Query" 'append query for the table'
 
 Set rst = MyDB.OpenRecordset("Case Management File", dbOpenSnapshot)
 
 Open "C:\Test\Case Management.csv" For Output As #1 'location for csv file from the table'
 
 rst.MoveFirst
 
 With rst
   'Write Field names to Output File, delimiting by '|'
   For intFldCtr = 0 To .Fields.Count - 1
     strBuild = strBuild & .Fields(intFldCtr).Name & "|"
   Next
     Print #1, Left$(strBuild, Len(strBuild) - 1)    'Field Names, remove ending ' | '
       strBuild = ""     'Must RESET
 
   Do While Not .EOF     'Values in Fields delimited by '|'
     For intFldCtr = 0 To .Fields.Count - 1
       strBuild = strBuild & .Fields(intFldCtr).Value & "|"
     Next
       Print #1, Left$(strBuild, Len(strBuild) - 1)      'Each Record, remove ending ' | '
         strBuild = ""     'Must RESET for Next Record
           .MoveNext
   Loop
 End With
 
 rst.Close
 Set rst = Nothing

 
Close #1
End Function
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Also add:

 Set MyDB = Nothing

  The issue with hanging references is that Access cannot determine if it was opened as an automation server or not.   If it goes to quit and still has a reference open, it will hang.

Jim.

Author

Commented:
Thanks but still not closing.  Is there a way I can see what is failing with a macro's execution?

Author

Commented:
Okay I got it to work by putting the "Application.Quit" as the last line in the last function the Macro calls.  When you 1st suggested it, I had that command as it's own separate function for the Macro to use.

If I leave this command as part of the last function being processed, is that kind of susceptible to damaging the database (like pulling a power cord)?

Here's where I have it:

Option Compare Database

Public Function ExportCaseManagement()


Dim MyDB As DAO.Database
 Dim rst As DAO.Recordset
 Dim intFldCtr As Integer
 
 Set MyDB = CurrentDb
 
 
 MyDB.Execute "Delete * FROM [Case Management File]" ' delete contents so an append can populate tables'
 
 MyDB.Execute "7 Case Management Query" 'append query for the table'
 
 Set rst = MyDB.OpenRecordset("Case Management File", dbOpenSnapshot)
 
 Open "C:\Test\Case Management.csv" For Output As #1 'location for csv file from the table'
 
 rst.MoveFirst
 
 With rst
   'Write Field names to Output File, delimiting by '|'
   For intFldCtr = 0 To .Fields.Count - 1
     strBuild = strBuild & .Fields(intFldCtr).Name & "|"
   Next
     Print #1, Left$(strBuild, Len(strBuild) - 1)    'Field Names, remove ending ' | '
       strBuild = ""     'Must RESET
 
   Do While Not .EOF     'Values in Fields delimited by '|'
     For intFldCtr = 0 To .Fields.Count - 1
       strBuild = strBuild & .Fields(intFldCtr).Value & "|"
     Next
       Print #1, Left$(strBuild, Len(strBuild) - 1)      'Each Record, remove ending ' | '
         strBuild = ""     'Must RESET for Next Record
           .MoveNext
   Loop
 End With
 
 rst.Close
 Set rst = Nothing
 Set MyDB = Nothing
 
 
Close #1

Application.Quit
End Function

Open in new window

John TsioumprisSoftware & Systems Engineer

Commented:
Application.Quit does the jobs as its the most powerful.
I would recommended this but the way your question was written made me thought that you are working on macro - level not VBA
Following on Jim's advice...don't leave loose ends...what is open should close...and then set to Nothing....its a good practice and it is one
less possible problem to worry about.
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
<<If I leave this command as part of the last function being processed, is that kind of susceptible to damaging the database (like pulling a power cord)?>>

 No, it's still an orderly shutdown of the database.

<<I had that command as it's own separate function for the Macro to use.>>

 Should have worked fine that way as well.

Jim.

Author

Commented:
Thanks Jim for the assistance and thank you John for your comments as well.  I have it functioning as needed and will play around some more with a copy of the application to see if I can improve it and have it close out through the macro calling it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial