[AccessD] A2K: On error goto next

Steve,

  I see Doug's already given you a fix, but I wanted to kick in a few
comments on the code in general:

1. Use of DB_OPEN_SNAPSHOT - I would not do this.  To create a snapshot,
Access must copy every record to a temp file on your local disk.  If these
recordsets are large, your going to get very poor performance.

2. The cleanup routine - Besides trying to close the recordset, you should
also be setting the object variables to nothing:

tCount_Exit:
    On Error Resume Next

    rstLookup.Close
    Set rstLookup = Nothing

    Set dbCurrent = Nothing

    Exit Function

3. This may not be wrong, but I'm a bit confused as to why if a criteria is
passed your working with a global database reference, but if you don't your
use local variables.

4. I don't remember anything special about snapshots, but normally you need
to do a .MoveLast, not a .MoveFirst to accuratly determine the number of
records in a set.  Chances are .RecordCount will be accurate though as to
create the snapshot, JET copies every record so what you have may be fine.

5. It's a myth that domain functions are always slower.  In some cases they
are as fast or faster then other methods.  You really should try seeing if
some of them would yield better results.

Jim Dettman
President,
Online Computer Services of WNY, Inc.
(315) 699-3443
jimdettman at earthlink.net

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Steven W.
Erbach
Sent: Monday, July 07, 2003 12:38 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] A2K: On error goto next


Jim,

Here's the full tCount() function:

' Created   : 1 Feb 1996 T.Best
Function tCount(pstrField As String, pstrTable As String, pstrCriteria As
String) As Long

    Dim dbCurrent As DAO.Recordset
    Dim rstLookup As DAO.Recordset
    Dim lngCount As Long

    On Error GoTo tCount_Err

    Set dbCurrent = DBEngine(0)(0)
    If pstrCriteria = "" Then
       If pstrField = "*" Or pstrField = "" Then
          Set rstLookup = dbCurrent.OpenRecordset("Select Count(*) From " &
_
            pstrTable, DB_OPEN_SNAPSHOT)
       Else
          Set rstLookup = glob.gDbs.OpenRecordset("Select Count(" &
pstrField & ") From " & _
            pstrTable, DB_OPEN_SNAPSHOT)
       End If
    Else
       If pstrField = "*" Or pstrField = "" Then
          Set rstLookup = glob.gDbs.OpenRecordset("Select Count(*) From " &
_
            pstrTable & " Where " & pstrCriteria, DB_OPEN_SNAPSHOT)
       Else
          Set rstLookup = glob.gDbs.OpenRecordset("Select Count(" &
pstrField & ") From " & _
            pstrTable & " Where " & pstrCriteria, DB_OPEN_SNAPSHOT)
       End If
    End If
    If Not rstLookup.BOF Then
       rstLookup.MoveFirst
       lngCount = rstLookup(0)
    Else
       lngCount = 0
    End If
    rstLookup.Close
    tCount = lngCount

tCount_Exit:
    On Error Resume Next
    rstLookup.Close
    Exit Function

tCount_Err:
    Select Case Err
       Case Else
    End Select

        ' Retry/Abort/Ignore

    Select Case MsgBox(Error, MB_ABORTRETRYIGNORE Or MB_ICONEXCLAMATION, _
        "Error " & Err)
       Case IDABORT
          Resume tCount_Exit
       Case IDRETRY
          Resume
       Case IDIGNORE
          Resume Next
    End Select
End Function

Again, the problem occurs in the tCount_Exit subroutine after tCount has
been successfully set to lngCount in the body of the function. The On Error
Resume Next is simply ignored.

Thanks, Jim.

Regards,

Steve Erbach
Scientific Marketing
Neenah, WI

"Nothing so needs reforming as other people's habits." - Mark Twain


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com