Hi, what on earth is happening here? I am running an access project linked 
to SQL 7, and at the end of each bit of VBA I close off all the recordsets 
I've been using, to make doubly sure that they are all closed and are not 
using server resources. Some may already have been closed, so I put 'on 
error resume next' before just in case, as shown in the example below......


   Exit_Print_Timesheet:
        On Error Resume Next
        Emps.Close
        EmpDaysHist.Close
        Jobs.Close
        Set Emps = Nothing
        Set EmpDaysHist = Nothing
        Set Jobs = Nothing

Exit Sub

Now this works absolutely fine......except on ONE PC, which errors if it 
comes across a recordset that is already closed, saying something 
like "cannot do this operation on an object that is not open". Therefore 
it is IGNORING the on error statement! How is this possible? Is there some 
mad local Access setting that could cause this? If it did it on all 
machines I would not be as puzzled, but why only on the one? Any ideas?

Add the following procedure to any module or a new module.  You can then
call it as the last line of each procedure that opens a recordset

Public Sub CloseDBAndRs()
'---------------------------------------------
' Author: John Ruff
' Date: 2/14/2000
' Purpose: Close all open recordsets, _
            databases, and workspaces
'---------------------------------------------
' If there is no open workspace, database, _
  or recordset an error will be generated _
  when trying to close and release the variable. _
  Resume Next will bypass the error
On Error Resume Next
    Dim ws As Workspace
    ' If you are using ADO
    Dim db As ADO.Database
    Dim rs As ADO.Recordset
    ' If you are using DAO
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
      
    ' check for databases in each workspace
    For Each ws In Workspaces
        ' check for recordsets in each database
        For Each db In ws.Databases
            ' close any open recordsets and _
              release their variables
            For Each rs In db.Recordsets
              rs.Close
              Set rs = Nothing
            Next
            ' close any open databases and _
              release their variables
            db.Close
            Set db = Nothing
        Next
        ' close any open workspaces and _
          release their variables
        ws.Close
        Set ws = Nothing
    Next
  
  ' Reset error object to 0
  Err = 0

End Sub

John V. Ruff - The Eternal Optimist :-)
Always Looking For Contract Opportunities

www.noclassroom.com
Live software training
Right over the Internet

Home:  xxx.xxx.xxxx
Cell:  xxx.xxx.xxxx
9306 Farwest Dr SW
Lakewood, WA 98498

"Commit to the Lord whatever you do,
    and your plans will succeed."  Proverbs 16:3





-----Original Message-----
From: Alan Edwards [mailto:alan.edwards@n...] 
Sent: Tuesday, July 16, 2002 3:37 PM
To: Access
Subject: [access] Wierd error


Hi, what on earth is happening here? I am running an access project
linked 
to SQL 7, and at the end of each bit of VBA I close off all the
recordsets 
I've been using, to make doubly sure that they are all closed and are
not 
using server resources. Some may already have been closed, so I put 'on 
error resume next' before just in case, as shown in the example
below......


   Exit_Print_Timesheet:
        On Error Resume Next
        Emps.Close
        EmpDaysHist.Close
        Jobs.Close
        Set Emps = Nothing
        Set EmpDaysHist = Nothing
        Set Jobs = Nothing

Exit Sub

Now this works absolutely fine......except on ONE PC, which errors if it

comes across a recordset that is already closed, saying something 
like "cannot do this operation on an object that is not open". Therefore

it is IGNORING the on error statement! How is this possible? Is there
some 
mad local Access setting that could cause this? If it did it on all 
machines I would not be as puzzled, but why only on the one? Any ideas?

I assume that you are using Access 2000 (or later). From Access 2000 you can
set the behaviour on encountering an error:

Go to the module window (Alt F11) or open any module then >.
Tools/Options/General. Here, the "Error Trapping" can be set to one of:

1) "Break on All Errors"

2) "Break in Class Module"

3) "Break on Unhandled Errors"

You need to select the third option.

This, of course needs to be set for each PC running Access.

Having said all that, I have never been a fan of relying on error trapping
(or NOT error trapping, as here) as an integral part of code. I would
suggest that you check the state of each recordset and only attempt to close
it if it is open.

With ADO, this would be:

    Exit_Print_Timesheet:
        'On Error Resume Next -- Preferably DO NOT use this technique
    If Emps.State = adStateOpen Then
        Emps.Close
    End If
etc.........
etc...........
etc......


Ian Ashton


-----Original Message-----
From: Alan Edwards [mailto:alan.edwards@n...]
Sent: Tuesday, July 16, 2002 3:37 PM
To: Access
Subject: [access] Wierd error


Hi, what on earth is happening here? I am running an access project linked
to SQL 7, and at the end of each bit of VBA I close off all the recordsets
I've been using, to make doubly sure that they are all closed and are not
using server resources. Some may already have been closed, so I put 'on
error resume next' before just in case, as shown in the example below......


   Exit_Print_Timesheet:
        On Error Resume Next
        Emps.Close
        EmpDaysHist.Close
        Jobs.Close
        Set Emps = Nothing
        Set EmpDaysHist = Nothing
        Set Jobs = Nothing

Exit Sub

Now this works absolutely fine......except on ONE PC, which errors if it
comes across a recordset that is already closed, saying something
like "cannot do this operation on an object that is not open". Therefore
it is IGNORING the on error statement! How is this possible? Is there some
mad local Access setting that could cause this? If it did it on all
machines I would not be as puzzled, but why only on the one? Any ideas?

Thanks, works a treat now!