Close All Outstanding Db Connections

[gray]

Hi

Access 2002/2007

I have a problem when closing Access. Some of my forms are based on ADBDB Rs's on which I use SQL transactions. I build a string (SQLLine) in the form-open and then add the ADODB Rs to the form. So.....

 Set Edit_Cnn = CurrentProject.Connection
Edit_Cnn.BeginTrans
Edit_Transaction_Begun = True
 
'Create an instance of the ADO Recordset class and set its properties
Set Edit_rstADO = New ADODB.Recordset
With Edit_rstADO
        Set .ActiveConnection = Edit_Cnn
        .Source = SQLLine
        .LockType = adLockOptimistic
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
        .Open
End With
 
Set CntrlBx.Form.Recordset = Edit_rstADO

My Error Handling includes cancellation(s) of the changes. So .....

 If Edit_Transaction_Begun Then
        Edit_Cnn.RollbackTrans
        Edit_Transaction_Begun = False
End If
 
'Close the ADO connection we opened
Edit_Cnn.Close
Set Edit_Cnn = Nothing

If successful, I commit and close the connection in an 'Apply' button click event.... so....

    If Edit_Transaction_Begun Then
        Edit_Cnn.CommitTrans
        Edit_Transaction_Begun = False
    End If
 
   'Close the ADO connection we opened
    Edit_Cnn.Close
    Set Edit_Cnn = Nothing

If user cancels then I rollback and close...

    If Edit_Transaction_Begun Then
        Edit_Cnn.RollbackTrans
        Edit_Transaction_Begun = False
    End If
 
   'Close the ADO connection we opened
    Edit_Cnn.Close
    Set Edit_Cnn = Nothing

In any of the cases, the CNX should get closed.

However, if I do experience a problem (which is a lot when developing) and try to close Access (using 'x') it hangs up and I have to use Task Manager to kill it. I'm pretty sure this is cos' of an outstanding CNX?

Does anyone know how to cycle around any/all open connections and close them? I could run that in a 'close' macro. I look around but think I must be on Access' leading edge!

thanks

 

[spikepl]

If your theory is right then you seemingly need a third option to Apply/Cancel on each form. That is when X is pressed. Check in On Unload or Close, that run also when the form, or db, is closed by x. Or just call your Cancel-code from there.

 

[gray]

Hi

Sorry.. might not have given enough info there.... the forms employing the CNX/SQL Trans etc all have their 'x' control boxes disabled... so they have to press one of my custom Apply or Cancel buttons.

These forms are modal being opened by a call in another form. When they are applied/ cancelled/ failed, Access drops back to the calling form.... so one way or another the Edit_Cnn.Close and Set Edit_Cnn = Nothing are always called.... I use 'always' in the loosest sense...

At the moment Access is closed by clicking it's own 'x' (rather then a form's 'x').

I've been trying to gen myself up with this lot but is incredibly convoluted to the layman... with new workspaces being created within CNXs and so on... so even though one appears to be using CurrentProject.Connection it's probably an automatically generated new workspace within that... which leaves the old one running.... ???? ... well maybe/maybe not...

I think I've tripped into some real expert level Access....

 

[spikepl]

No. You have dug a hole for yourself and wallow in it. Whether you removed the X or not, the forms still run the Close/Unload events, which you could use to re-run your closing code, unless it already had run.

 

[gray]

Oh yes. I see... I'm with you.... I'll move the closing of the CNX to the form's unload event and try it there for a while... see if it improves.... will update later.... thnx

 

[gray]

Well the 'locking up' of Access seems to happen less often but it's still a major pain...

Sometimes when forms crash the CNX does not get closed. When I try Access 'X' button, it sometimes does not close... but it does prevent any other action beoing taken in Access (it 'freezes' it). I then have to kill Access using task manager. When I re-open it, it has often lost my latest work (I guess it opens at some checkpoint it took somewhere).

I think I need to construct a macro that will go thru' any open CNXs and force-close them before I hit the Access 'X' button.