Solved

Error handling is MS Access

Posted on 2013-01-05
6
317 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 26

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 26

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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 …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now