Close ADO Connection for EXEC adAsyncExecute 1

[smedvid]

I have a button in an MS Access form that launches a SQL server SP Async. The users can the view the form for status update on a log table that is set to refresh per form Timer(). User can also open other MS Access forms to run other processes.

Question: How can I properly close the ADO DBConn Connection? I think it remains open until I close the form. I would rather be clean and close after the Log table is updated with Finished; perhaps close the DBConn in Timer()? But, object is not

Private Sub cmd_Run_Report1_Click()
    Dim DBConn          As New ADODB.Connection
...
    DBConn.ConnectionString = AutoExec.APTConnect
    DBConn.CommandTimeout = 0
    DBConn.ConnectionTimeout = 0
    DBConn.Open
...    
    lcSQL = "EXEC APOLY.sp_Build_Report1 " & lcParameters
    DBConn.Execute lcSQL, , adAsyncExecute
...

I thought I would somehow check connection state in Timer(); and then check if log table is filled with Finished status for process. But DBConn is not valid?

Private Sub Form_Timer()
...
    If DBConn.State <> adStateOpen Then
        MsgBox ("HERE")
    End If
...

Any assistance is appreciated... tia,

Steve Medvid
IT Consultant & Web Master

 

[PWise]

declare DBConn
in the form declartion not in the command click

 

[smedvid]

PWise,
That is the clue I needed to solve the mystery! Excellent help and fast response are greatly appreciated....



Steve Medvid
IT Consultant & Web Master

 

[oharab]

You could also use the events raised by the connection.
At the top of your form:

private withevents DBConn as ADODB.Connection

you can then handle the ExecuteComplete event that is raised when the connection has finished:

Private Sub DBConn_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
 MsgBox "Finished executing. I affected " & RecordsAffected & " records."
 DBConn.Close
End Sub

This saves you the overhead of having a timer running and having to remember to start it etc.

All the events available are detailed here.

----------------------------------------------
Ben O'Hara

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.