VB routine leaves msaccess.exe running after quit

[dave fernandez]

I put the following function into an Access 2000
database. Now, when I quit the database, an instance of
the msaccess.exe process remains running. If I re-open
the database, reaccess the routine and quit again, there
are two instances running. If I open and use the database
but don't access the routine, msaccess.exe goes away.
Help please! Here is the routine:

Option Compare Database

Function FindSERecord(SONUMBERX, SOTYPEX, LINENUMBERX,
QUANTITYX) As Integer

On Error GoTo FindSERecord_Err

Dim CurConn As ADODB.Connection
Dim rst As ADODB.Recordset

' 0 = Record Found
' 1 = Record Added
' 2 = Error
FindSERecord = 0

Set CurConn = CurrentProject.Connection
Set rst = New ADODB.Recordset

rst.Open "SOEXTENSION", CurConn, adOpenKeyset,
adLockOptimistic, adCmdTableDirect

' Select the index used to order the data in the recordset
rst.Index = "PrimaryKey"

' Find the order where OrderId = 10255 and ProductId = 16
rst.Seek Array(SONUMBERX, SOTYPEX, LINENUMBERX),
adSeekFirstEQ

If rst.EOF Then
FindSERecord = 1
rst.AddNew
rst!SONUMBER = SONUMBERX
rst!SOTYPE = SOTYPEX
rst!LINENUMBER = LINENUMBERX
rst!GMT = QUANTITYX
rst.Update
rst.Close
CurConn.Close
Set rst = Nothing
Set CurConn = Nothing
Else
FindSERecord = 0
rst.Close
CurConn.Close
Set rst = Nothing
Set CurConn = Nothing
End If

FindSERecord_Err:

If Not rst Is Nothing Then
If rst.STATE = adStateOpen Then rst.Close
End If
Set rst = Nothing

If Not CurConn Is Nothing Then
If CurConn.STATE = adStateOpen Then CurConn.Close
End If
Set CurConn = Nothing

If Err <> 0 Then
MsgBox Err.Number & " " & Err.Description
FindSERecord = 2
End If

End Function

 

[Joe Fallon]

I think something is not being closed and the held reference is waht keeps
Access "running".
(This is a well-known problem.)
You have to close everything.
A quick glance at your code shows you are trying to do this, but maybe there
is a flaw in your routine that is hard to spot.

I use this technique to clean up my code routines.
I figure that if there was an error and I am already in the error handler,
then I just want to close everything regardless if it really exists anymore.
So I turn off the error handler and then clsoe each object. If it is already
destroyed, then it just moves on to the next step anyway.

FindSERecord_Err:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
SysCmd acSysCmdClearStatus
Exit Function