Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Error handling is MS Access

Posted on 2013-01-05
6
327 Views
Last Modified: 2013-01-10
In a given function or subroutine it is possible that multiple recordsets may be opened and closed, many variables intialized and used.

If the error handler in this sub or function is invoked it must handle 'known' errors appropriately.  In some case an unexpected error is encounter for which there is no known recovery and the only choice is to exit the app.

It's easy enough to exit the app but prior to this exit it would be much more appropriate to 'clean up' any opend object prior to the exit.  The objects in use at the time of the error are variable depending at what point in the logic the error was thrown.  I know if the sub is exectued to completion it will have used, recordsets Rs1, Rs2, Rs3 and Rs4.

When I get to the error handler some of these may have been opened and others not. The objects used in the sub or function may not only include recordsets and variable but als MS Excel that is being used to export a spreadhseet using a template, MS Outlook that is being used to generate and email and of course forms and reports.

Question:
Is there a generic way to find all open objects and close them prior to exiting the app.
0
Comment
Question by:mlcktmguy
6 Comments
 
LVL 27

Accepted Solution

by:
MacroShadow earned 200 total points
ID: 38747514
The following code was posted on bytes, it will close all open database objects:
Public Sub CloseAll()
    Dim aob As AccessObject
    With CurrentData
       ' "Tables"
       For Each aob In .AllTables
           If aob.IsLoaded Then
               DoCmd.Close acTable, aob.Name, acSaveYes
           End If
       Next aob
     
       ' "Queries"
       For Each aob In .AllQueries
           If aob.IsLoaded Then
               DoCmd.Close acQuery, aob.Name, acSaveYes
           End If
       Next aob
    End With
     
     
    With CurrentProject
       ' "Forms"
       For Each aob In .AllForms
           If aob.IsLoaded Then
               DoCmd.Close acForm, aob.Name, acSaveYes
           End If
       Next aob
     
       ' "Reports"
       For Each aob In .AllReports
           If aob.IsLoaded Then
               DoCmd.Close acReport, aob.Name, acSaveYes
           End If
       Next aob
     
       ' "Pages"
       For Each aob In .AllDataAccessPages
           If aob.IsLoaded Then
               DoCmd.Close acDataAccessPage, aob.Name, acSaveYes
           End If
       Next aob
     
       ' "Macros"
       For Each aob In .AllMacros
           If aob.IsLoaded Then
               DoCmd.Close acMacro, aob.Name, acSaveYes
           End If
       Next aob
     
       ' "Modules"
       For Each aob In .AllModules
           If aob.IsLoaded Then
               DoCmd.Close acModule, aob.Name, acSaveYes
           End If
       Next aob
    End With
End Sub

Open in new window

0
 
LVL 27

Assisted Solution

by:MacroShadow
MacroShadow earned 200 total points
ID: 38747518
See this for closing open recordsets.
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 100 total points
ID: 38747777
I generally use something like:
Private Sub Whatever

    On Error GoTo ProcError

'do something here

ProcExit:
    if not rs4 is nothing then 
        rs4.close
        set rs4 = nothing
    end if

    if not rs3 is nothing then
        rs3.close
        set rs3 = nothing
    end if

     'close others using the same steps as above

     Exit Sub

ProcError:

    if Err.Number = X Then
         'process known errors
         'Resume or Resume Next
   Else
         msgbox err.number & vbcrlf & err.description, vbokonly, "Unknown error"
         Resume ProcExit
    end if

End sub

Open in new window

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 100 total points
ID: 38748407
You should also include a On Error Resume Next tag at the beginning of your error handler:

Proc_Err:
  On Error Resume Next
  '/ Handle your errors here

You should also do the same if you have an "Exit" handler, like fyed posted above:

Proc_Exit:
  On Exit Resume Next
  '/ do your cleanup here

It seems that newer versions of Access (2007/2010) do a better job at cleaning up than older ones. However, you should still adhere to the old axiom: "If you built it, you should remove it". So any objects you you Dimension, you should also handle appropriately (i.e. Set YourVar = Nothing, or whatever makes sense for that object type). You don't need to do this with base types, like String or Number.
0
 
LVL 20

Assisted Solution

by:clarkscott
clarkscott earned 100 total points
ID: 38754561
For every procedure I write... I include a label called "progout:"
'======================================================
...code...
... code...

progout:
on error resume next
'--- close all objects in proc --
rst.close
rst1.close
db.close
set rst = nothing
set rst1 = nothing
set db = nothing
exit sub ' --(or function)
'=======================================================

ERROR HANDLING -
What I do is use a single error handler to handle most errors.   It involves creating a variable (verror).

'=========================
dim verror as boolean
'==========================

My error handler is this:

============================
err_verror:
       verror = true
       resume next
'============================

Now... the code you write you will expect only certain error conditions to occur.  For instance... opening a query as a recordset.   So....

'===============================================
on error goto err_verror
'-- code to open an Excel Workbook.....
if verror = true then
       '----- something went wrong....  so - what do you want to do?
       msgbox "Error opening workbook",vbcritical
       '----- this takes you to your progout and closes all opened stuff --------
       goto progout
end if
'==============================================

Perhaps you simply want to see if something exists and if it doesn,t, you want to do something else....

'==========================================================
on error goto err_verror
'-- code to open an Excel Workbook.....
if verror = true then
       '----- something went wrong....  so - what do you want to do?
       if msgbox ("Error opening workbook. Click CANCEL to keep running this app",vbcritical+vbokcancel) = vbOK then
              '----- this takes you to your progout and closes all opened stuff --------
               goto progout
       else
               '---- reset the verror variable and keep going......
               verror = false
        end if
end if
==================================================================

It's a little busier that some huge master error handler that includes every possible error number.... and to be honest.... there are few instances in complex code that will require the exact same response/handling each time.

Scott C
0
 
LVL 1

Author Closing Comment

by:mlcktmguy
ID: 38765132
All excellent answers.  I raised the points so I could distirbute more points to responders.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question