Improve company productivity with a Business Account.Sign Up

x
?
Solved

Error handling is MS Access

Posted on 2013-01-05
6
Medium Priority
?
348 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 28

Accepted Solution

by:
MacroShadow earned 800 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 28

Assisted Solution

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

Assisted Solution

by:Dale Fye
Dale Fye earned 400 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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
LVL 86

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 400 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 400 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
If you need to implement application level security in an Access database application or other VBA code, I strongly encourage you to take advantage of Active Directory groups.
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 …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

608 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