Thread: Build a Delete Query in VBA

Hello,

I am trying to build a delete query that will run off of the transaction numbers listed in another query. So far I have not been able to do this with a simple query. I thought it might be easier/possible in VBA. Currently I am running into errors when I try and set my recordset. Any tips would be greatly appreciated!!

Sub DeleteForm()
    
    Dim rs As DAO.Recordset
    Dim fld As DAO.Field
    Dim sql As String
    Dim sql2 As String
    
    sql = "SELECT q_BackDateI.TransNum FROM q_BackDateI"

    Set rs = CurrentDb.OpenRecordset(sql)
    
    For Each fld In rs.Fields
    
        sql2 = "DELETE t_Withhelds.* FROM t_Withhelds Where(((t_Withhelds.TransNum) = " & fld & "));"
        
    Next

    
End Sub

The code example above is great, I use it often.
Just in case your looking for a slightly different point of view, this is something I used to create large custom reports in Excel.
The Access user interface takes selected inputs and then builds multiple custom local Access tables. These are later called in a final SQL statement.
170 deletes all records (*) from the table. It could also be modified for more selection.
In my unique case, an autocounter links multiple tables.
So, after deleting all records, the autocounter needs to be reset. Otherwise, the deleted records last autocounter number persist. Here is an example of that code.


160 DoCmd.SetWarnings False
170 DoCmd.RunSQL ("Delete * From 08_Team_Permit_Federal_Excel_Local")
180 Call ChangeSeed("08_Team_Permit_Federal_Excel_Local", "CounterField", 1, 1)
' remember to turn SetWarning back to True later

'   WARNING **** This will NOT work on linked tables
'   Use to reset the Autocounter after deleting records in a LOCAL temp table
'   08_Team_Permit_Federal_Excel_Local
'   08_Team_Permit_State_Excel_Local and six others
'   for a very complex Permit report
Public Sub ChangeSeed(strTableName As String _
                           , strFieldName As String _
                           , lngStartAt As Long _
                           , intIncrementBy As Integer)
 
    Dim strSql As String
 
    'Build the SQL statement
    strSql = "ALTER TABLE " & strTableName & _
             " ALTER COLUMN " & strFieldName & _
             " COUNTER (" & lngStartAt & "," & intIncrementBy & ")"
' Debug.Print strSql           ' uncomment for testing purposes
    'Execute the SQL statement
    CurrentProject.Connection.Execute strSql, , adCmdText
 
End Sub

If this code was useful, a thank you would be appreciated