I have had problems using stored delete/update queries that involve changing data in SQL tables. The query would run without returning any error. It would also indicate SQL table rows have been deleted or changed. But when the SQL table is re-opened the records would reappear or revert to their original state.

To avoid this issue entirely I have written code that uses DAO objects instead of stored queries (other than SELECT queries). The following is a sketch of this technique:

Module Level: ' 'NOTE: This procedure contains line break characters 'for readability. These characters are not supported 'in Access Basic. ' Option Compare Database 'Use database order for string comparisons Option Explicit

Dim m_wsDAO As WorkSpace Dim m_dbDAO As Database

Dim m_varReturn As Variant Dim m_Transaction As Integer Dim m_intObj As Integer

Dim m_strMsgBox As String, m_strFileName As String

Sub DailyCalc_CleanSQLtbls () ' 'This sub clears out temporary Shaw CUSIPs from SQL Server 'tables ejv_cusip and output_oas. Without this procedure 'duplicate Security Names can show up 'in qselPortfolio* queries. ' Dim dbSQLServer As Database, rsDAO As Recordset Dim tSQL As String

On Error GoTo DailyCalc_CleanSQLtbls_Err

m_intObj = m_wsDAO Is Nothing
If m_intObj Then
    MsgBox "This sub needs workspace and database objects!"
    Exit Sub
End If

m_intObj = m_dbDAO Is Nothing
If m_intObj Then
    MsgBox "This sub needs a database object!"
    Exit Sub
End If

Set dbSQLServer = m_wsDAO.OpenDatabase("", _
    False, False, ODBC_CONNECT_STRING)
Set rsDAO = m_dbDAO.OpenRecordset("qselOA_not_Taz", _
    DB_OPEN_DYNASET, DB_READONLY)

While Not rsDAO.EOF

    tSQL = "delete from ejv_cusip "
    tSQL = tSQL & "where cusip = '" & rsDAO("cusip") & "'"

    dbSQLServer.Execute tSQL, DB_SQLPASSTHROUGH

    rsDAO.MoveNext
Wend

Set rsDAO = m_dbDAO.OpenRecordset("qselOA_not_Taz", _
    DB_OPEN_DYNASET, DB_READONLY)

While Not rsDAO.EOF

    tSQL = "delete from output_oas "
    tSQL = tSQL & "where cusip = '" & rsDAO("cusip") & "'"

    dbSQLServer.Execute tSQL, DB_SQLPASSTHROUGH

    rsDAO.MoveNext
Wend

DailyCalc_CleanSQLtbls_Exit: m_intObj = dbSQLServer Is Nothing If Not (m_intObj) Then dbSQLServer.Close Exit Sub

DailyCalc_CleanSQLtbls_Err: Select Case Err Case Else MsgBox Err & ": " & Error$(Err), 16, _ "DailyCalc_CleanSQLtbls Error" Resume DailyCalc_CleanSQLtbls_Exit

End Select

End Sub