By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,140 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Deleting records from multiple tables

Midzie
P: 25
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.

Expand|Select|Wrap|Line Numbers
  1. StrSQL = ""
  2.  
  3. PurgeMsg = MsgBox("Do you want to purge data?", 1, "Purging Data")
  4.     If PurgeMsg = 1 Then
  5.         'delete from order
  6.         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); "
  7.         'delete from Schedule
  8.         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); "
  9.         'delete from Note
  10.         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); "
  11.         'delete from Yearly
  12.         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); "
  13.         'delete from Closed
  14.         StrSQL = StrSQL & "DELETE FROM [Closed] WHERE [CompDate] BETWEEN NOW AND DATEADD('yyyy', -2, NOW)"
  15.  
  16.         MsgBox "Purging Successful!"
  17.     Else
  18.         MsgBox "Purging Failed!"
  19.     End If
Feb 20 '12 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. Dim cdb As DAO.Database
  3.  
  4. Set cdb = CurrentDb
  5. StrSQL = ""
  6. If MsgBox(Prompt:="Do you want to purge data?", _
  7.           Buttons:=vbYesNo, _
  8.           Title:="Purging Data") = vbYes Then
  9.     'delete from order
  10.     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()"
  11.     Call cdb.Execute(strSQL)
  12.     'delete from Schedule
  13.     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()"
  14.     Call cdb.Execute(strSQL)
  15.     'delete from Note
  16.     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()"
  17.     Call cdb.Execute(strSQL)
  18.     'delete from Yearly
  19.     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()"
  20.     Call cdb.Execute(strSQL)
  21.     'delete from Closed
  22.     StrSQL = "DELETE FROM [Closed] WHERE [CompDate] Between DateAdd('yyyy', -2, Date()) And Date()"
  23.     Call cdb.Execute(strSQL)
  24.     MsgBox "Purging Successful!"
  25. Else
  26.     MsgBox "Purging Failed!"
  27. End If

Share this Question
Share on Google+
3 Replies


Seth Schrock
Expert 2.5K+
P: 2,931
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.
Feb 20 '12 #2

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. Dim cdb As DAO.Database
  3.  
  4. Set cdb = CurrentDb
  5. StrSQL = ""
  6. If MsgBox(Prompt:="Do you want to purge data?", _
  7.           Buttons:=vbYesNo, _
  8.           Title:="Purging Data") = vbYes Then
  9.     'delete from order
  10.     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()"
  11.     Call cdb.Execute(strSQL)
  12.     'delete from Schedule
  13.     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()"
  14.     Call cdb.Execute(strSQL)
  15.     'delete from Note
  16.     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()"
  17.     Call cdb.Execute(strSQL)
  18.     'delete from Yearly
  19.     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()"
  20.     Call cdb.Execute(strSQL)
  21.     'delete from Closed
  22.     StrSQL = "DELETE FROM [Closed] WHERE [CompDate] Between DateAdd('yyyy', -2, Date()) And Date()"
  23.     Call cdb.Execute(strSQL)
  24.     MsgBox "Purging Successful!"
  25. Else
  26.     MsgBox "Purging Failed!"
  27. End If
Feb 20 '12 #3

Midzie
P: 25
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.
Feb 21 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.