Comments by cotsjdixon (Top 14 by date)

This did not work. It does not appear that the application is releasing the database even with this logic. There must be some way to force the database to close.

Here is the query that I extract from the database.

<pre lang="text">"INSERT INTO [CHRTV-SUB1] ( DSRECNUMFIX, SumOfACV, SumOfAHELO, SumOfARTY, SumOfAVLB, SumOfBT, SumOfCAIR, SumOfCSHELO, SumOfLAL, SumOfPTAIR, SumOfRCCTAIR, SumOfUTHELO, EQTABBR, EQTRECNUM, V_CATORD, [SESSION] )
SELECT CHRTV.DSRECNUMFIX, Sum(CHRTV.ACV) AS SumOfACV, Sum(CHRTV.AHELO) AS SumOfAHELO, Sum(CHRTV.ARTY) AS SumOfARTY, Sum(CHRTV.AVLB) AS SumOfAVLB, Sum(CHRTV.BT) AS SumOfBT, Sum(CHRTV.CAIR) AS SumOfCAIR, Sum(CHRTV.CSHELO) AS SumOfCSHELO, Sum(CHRTV.LAL) AS SumOfLAL, Sum(CHRTV.PTAIR) AS SumOfPTAIR, Sum(CHRTV.RCCTAIR) AS SumOfRCCTAIR, Sum(CHRTV.UTHELO) AS SumOfUTHELO, CHRTV.EQTABBR, CHRTV.EQTRECNUM, EQPMT.[5_CATORD], CHRTV.SESSION
FROM (CHRTV INNER JOIN (SELECT [EQTRECNUM], [SESSION], [5_CATORD]
FROM [EQUIPMNT], (SELECT * FROM [EQTCAT_ORD] IN [REFERENCELOCATION] WHERE [TREATY] = 'CFE') [REFEQTORD]
WHERE ([EQUIPMNT].[EQTCAT] =[REFEQTORD].[CAT])) [EQPMT]
ON (CHRTV.EQTRECNUM = EQPMT.EQTRECNUM) AND (CHRTV.SESSION = EQPMT.SESSION))
WHERE (([CHRTV].[SESSION]=[SESSIONPARAMETER]) AND ([CHRTV].[OOVSTATUS]<>'X'))GROUP BY CHRTV.DSRECNUMFIX, CHRTV.EQTABBR, CHRTV.EQTRECNUM, EQPMT.[5_CATORD], CHRTV.SESSION"</pre>

Then the code replaces the [SESSIONPARAMETER] and [REFERENCELOCATION] with the appropriate values. The code performs the following code to execute the resulting sql statement:
<pre lang="text"> Public Function ExecStatement(ByVal sql As String) As Boolean
Dim cmd As New OleDbCommand
Dim rtn As Boolean = False
Dim closeOnExit As Boolean = Not _connOpen
Try
If Not _connOpen Then OpenConnection()
cmd.Connection = _cnn
cmd.CommandText = sql
cmd.ExecuteNonQuery()
rtn = True
Catch ex As Exception
Throw New Exception(String.Format("Failed to execute statement. {0}SQL: {1}{0}Message: {2}", vbCrLf, sql, ex.Message))
Finally
If closeOnExit Then CloseConnection()
End Try
Return rtn
End Function
</pre>

As I mentioned earlier, in the non-ADO version, when the sql statement is executed I get rows returned where the OOVSTATUS is an empty string (the check of <>"X" works) but in this ADO.Net version the rows that have an empty string for OOVSTATUS are not returned. I have also verified through some test code that the data is empty, not null.

The connection string for both versions is:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};User ID=admin; Password=", DBPhysicalFile()