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 FunctionThis 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.

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?

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.

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.

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

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.

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.

I found this thread (http://groups.google.com/group/microsoft.public.access/browse_thread/thread/54789c9bd5428b59/2f069f862aa35db0?tvc=2&q=process+list+access+microsoft&hl=en#2f069f862aa35db0) 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).

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.

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.