Deleting records from multiple tables

[Midzie]

Hi All, first of all please don't delete my post... I need some answers on it that's why I posted a question here. If anyone there could help me please, I am deleting records from multiple tables but it doesn't work for me.

StrSQL = ""
 
PurgeMsg = MsgBox("Do you want to purge data?", 1, "Purging Data")
    If PurgeMsg = 1 Then
        'delete from order
        StrSQL = StrSQL & "DELETE [Order] FROM [Order] A INNER JOIN [Closed] B ON A.[Wnum] = B.[Wnum] WHERE B.[CompDate] BETWEEN NOW AND DATEADD('yyyy', -2, NOW); "
        'delete from Schedule
        StrSQL = StrSQL & "DELETE [Schedule] FROM [Schedule] A INNER JOIN [Closed] B ON A.[PNum] = B.[Wnum] WHERE B.[CompDate] BETWEEN NOW AND DATEADD('yyyy', -2, NOW); "
        'delete from Note
        StrSQL = StrSQL & "DELETE [Note] FROM [Note] A INNER JOIN [Closed] B ON A.[NoteNum] = B.[Wnum] WHERE B.[CompDate] BETWEEN NOW AND DATEADD('yyyy', -2, NOW); "
        'delete from Yearly
        StrSQL = StrSQL & "DELETE [Yearly] FROM [Yearly] A INNER JOIN [Closed] B ON A.[Wnum] = B.[Wnum] WHERE B.[CompDate] BETWEEN NOW AND DATEADD('yyyy', -2, NOW); "
        'delete from Closed
        StrSQL = StrSQL & "DELETE FROM [Closed] WHERE [CompDate] BETWEEN NOW AND DATEADD('yyyy', -2, NOW)"
 
        MsgBox "Purging Successful!"
    Else
        MsgBox "Purging Failed!"
    End If

[Seth Schrock]

First of all, you need to define 5 different StrSQL variables, one for each DELETE query. You can't run them all together as they are now. You also have to tell the VBA to run the SQL code. I have no way of knowing if your SQL code is good since the question doesn't give me enough information (possibly why your post has been deleted in the past?). What I would recommend doing is to create a query object and test your code in there. This allows you to see what is the problem if a problem does exist. You can then use VBA to open the queries using the DoCmd.OpenQuery command or you can paste the SQL code back into your VBA code. I personally would do the former just because I'm more familiar with this method and I don't remember how to trigger the SQL code to be performed. I have done it, but I would have to go look in some of my other projects tomorrow when I'm at work.

[NeoPa]

Hi Midzie.

First of all, posts are never deleted unless they break the rules of the site. I suggest you concentrate on asking questions properly rather than asking for exceptions to be made for you regarding site rules. We would rather answer your questions than delete them anyway. Sometimes you make that impossible though. I'm sure we can all be happy though, that your posts will never be deleted if they conform to the rules.

Please read Before Posting (VBA or SQL) Code. There are various threads which explain how questions should be asked. Please read at least one of them before submitting any other questions. It will help to ensure your questions don't get deleted in future.

This question is borderline, as you haven't even explained what you mean by :

Midzie:
I am deleting records from multiple tables but it doesn't work for me.

I'm guessing that nothing ever gets deleted, because there's actually no code there to do the deleting.

It may help to understand that SQL is not VBA. That sounds pretty obvious, but actually many beginners see code with SQL command data in strings, without realising that this command data doesn't ever do anything unless it is somehow passed to the SQL engine to be processed and executed. Your (VBA) code sets up the SQL strings ok, but never actually calls the SQL engine (Jet) to process them, which means nothing will happen.

Also, Jet SQL (used in Access) doesn't allow multiple SQL commands in the same invocation, so each DELETE query needs to be separate.

Try instead :

Dim strSQL As String
Dim cdb As DAO.Database
 
Set cdb = CurrentDb
StrSQL = ""
If MsgBox(Prompt:="Do you want to purge data?", _
          Buttons:=vbYesNo, _
          Title:="Purging Data") = vbYes Then
    'delete from order
    StrSQL = "DELETE [Order] FROM [Order] A INNER JOIN [Closed] B ON A.[Wnum] = B.[Wnum] WHERE B.[CompDate] Between DateAdd('yyyy', -2, Date()) And Date()"
    Call cdb.Execute(strSQL)
    'delete from Schedule
    StrSQL = "DELETE [Schedule] FROM [Schedule] A INNER JOIN [Closed] B ON A.[PNum] = B.[Wnum] WHERE B.[CompDate] Between DateAdd('yyyy', -2, Date()) And Date()"
    Call cdb.Execute(strSQL)
    'delete from Note
    StrSQL = "DELETE [Note] FROM [Note] A INNER JOIN [Closed] B ON A.[NoteNum] = B.[Wnum] WHERE B.[CompDate] Between DateAdd('yyyy', -2, Date()) And Date()"
    Call cdb.Execute(strSQL)
    'delete from Yearly
    StrSQL = "DELETE [Yearly] FROM [Yearly] A INNER JOIN [Closed] B ON A.[Wnum] = B.[Wnum] WHERE B.[CompDate] Between DateAdd('yyyy', -2, Date()) And Date()"
    Call cdb.Execute(strSQL)
    'delete from Closed
    StrSQL = "DELETE FROM [Closed] WHERE [CompDate] Between DateAdd('yyyy', -2, Date()) And Date()"
    Call cdb.Execute(strSQL)
    MsgBox "Purging Successful!"
Else
    MsgBox "Purging Failed!"
End If

[Midzie]

Hi Neopa and Seth, thanks for helping me. My purging button is already working. I have a follow up question on this, which can be found at How do I Log Multiple Values in One Message.