Code Creates Lingering Process

[TastyWheat]

After I close Access (via the 'X' or 'Exit') I still see MSACCESS.EXE in my process list. This only happens when I run this piece of code:

Public Function GetEmployeeStore(lngEmployee As Long) As Long
On Error GoTo GetEmployeeStore_Error
    
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strSQL As String
    Dim strTable As String
            
    Set conn = CurrentProject.Connection
    strTable = "[Store Change]"
    
    strSQL = "SELECT TOP 1 * " & _
             "FROM " & strTable & " " & _
             "WHERE EmployeeID = " & lngEmployee & " " & _
             "ORDER BY EffectiveDate Desc;"
    Set rs = conn.Execute(strSQL)
    
    GetEmployeeStore = rs("NewStoreID")
    
GetEmployeeStore_End:
    If Not (rs Is Nothing) Then
        If (rs.State = adStateOpen) Then
            rs.Close
        End If
    End If
    Set acc = Nothing
    Set rs = Nothing
    Set conn = Nothing

    Exit Function
GetEmployeeStore_Error:
    Debug.Print Err.Description & " (" & Err.Number & ")"

    GoTo GetEmployeeStore_End
End Function

This isn't an extra process that's being created. This is the same process from open to close. It just doesn't close when I tell it to.

 

[boblarson]

I see this but no Dim for it or instantiation:

Set acc = Nothing

Also, have you tried using

Set rs = New ADODB.Recordset
rs.Open strSQL, cnn, adOpenDynamic, adLockOptimistic

instead of using the Execute of the Connection?

 

[The_Doc_Man]

There is a basic rule that you should have learned in kindergarten. (What was that book, "Everything I Needed to Know, I Learned In Kindergarten"?)

Close whatever you open, come Hell or high water. Scrupulously close any and every structure you open. In particular, don't worry about whether something is open or not. Just close it with an On Error Then Continue (I think that's the correct syntax) in force before you do it.

Odds are that your process is hanging because at least one workspace is still open and therefore has a file handle open. There is this little thing called "Process Rundown" that requires all file handles to be closed before you can safely exit a process. For what it is worth, other operating systems have the same problem whether we are talking channels, handles, streams, or any other name for file connections.

 

[TastyWheat]

I see this but no Dim for it or instantiation:

Set acc = Nothing

Sorry, that was actually a bit of code I forgot to remove. I tried getting an application reference before getting my connection reference.

If it makes any difference the database I'm using is a secured one. I've used code like this hundreds of times and never had trouble. Again, if this makes any difference, this code comes from a module I created after I made and secured the database. I had similar code working fine before this, but I accidentally deleted it.

 

[john471]

My first (and only) suspicion is (as the_doc_man suggests, without actually spelling it out) that you need a

conn.close

in your exit section. As he also infers, your checks for an open rs are (AFAIK) not really necessary, a slight modification, if I may suggest...

GetEmployeeStore_End:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing

    Exit Function

HTH

Regards

John

 

[john471]

P.S.

I may well be out of line, 'cause I haven't worked with CurrentProject. I'm still stuck in AC97 most of the time. Apologies if so.

John.

 

[TastyWheat]

Jeez, it's worse than I thought. I cut down my code to see which line was causing the process to hang. It turns out, this code causes my process to linger as well:

Public Function GetEmployeeStore(lngEmployee As Long) As Long
    
    GetEmployeeStore = 111

End Function

Yes, that gives me the same problem, which leads me to believe it's a problem with the module and not the code. As I mentioned before, this module was created after my database was created and secured. Before that I never had this trouble.

 

[TastyWheat]

I found this thread in microsoft.public.access and it sounds kinda like my problem. I'm starting to think the problem has something to do with the data I'm passing in and not what happens inside the function. I'm calling the function like this:

=GetEmployeeStore([EID])

This goes into the control source field of a text box. "[EID]" is the employee id field of the employee table (not the name of a control).

 

[TastyWheat]

Just to update and finalize the thread I've abandoned the current (secured) database and continued working with the older (unsecured) one. I don't get the lingering process problem using my old database so security must have something to do with it. If there is a fix for it then it's probably too much hassle.

So I advise everyone to finish their databases before they decide to secure them.

 

[The_Doc_Man]

My thought is that if there is an ODBC link somewhere, it is not being closed correctly. I think your problem is the source of the data being transferred - OR the destination. Something is coming from outside or going outside of Access. It is the "outside" item that hangs the process.