1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: sql in access vba giving error

Discussion in 'Business Applications' started by BoaterJohn, Mar 15, 2015.

Thread Status:
Not open for further replies.
Advertisement
  1. BoaterJohn

    BoaterJohn Thread Starter

    Joined:
    Aug 2, 2011
    Messages:
    116
    I have a access sample database with table A and table B.
    I am trying to delete any records in table A with a matching [TicketDeliverNo] field in table B
    and if [AmtPaid] field in table B >1.
    I have a sub proc on the forms 'close event’ that will trigger a sql clauses to delete the records in table A.
    However, I’m getting a syntax error in the SQL statement, so the error states. I tried to no avail to fix it.
    Any help is highly appreciated,
    Private Sub Form_Close()
    Dim strSQL As String
    strSQL = "DELETE tblA.*" & _
    "FROM tblA" & _
    "Where tblA.[TicketDeliverNo] = tblB.[TicketDeliverNo]" & _
    "And tblB.[AmtPaid]>1;"
    DoCmd.RunSQL strSQL
    DoCmd.Close acForm, "FRMB"
    End Sub

    Thank You boaterjohn
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Try

    Private Sub Form_Close()
    Dim strSQL As String
    strSQL = "DELETE tblA.*" & _
    "FROM tblA" & _
    "Where tblA.[TicketDeliverNo] = tblB.[TicketDeliverNo]" & _
    "And tblB.[AmtPaid]>1"
    DoCmd.RunSQL strSQL
    DoCmd.Close acForm, "FRMB"
    End Sub
     
  3. BoaterJohn

    BoaterJohn Thread Starter

    Joined:
    Aug 2, 2011
    Messages:
    116
    OBP, thanks for response. Looks like you omitted the semi colon. I tried this and got the same error.
    "syntax error in FROM Clause" I believe that the semi colon is required using sql in vba proc. correct me if I'm in error of that. I attached the very small db so you could see the error when closing the form
     

    Attached Files:

  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I have found that with VBA generated SQL it does not need the ";" on the end.
    I have added a space after the FROM tblA
    so it now looks like this
    Dim strSQL As String
    On Error GoTo errorcatch

    strSQL = "DELETE tblA.*" & _
    "FROM tblA " & _
    "Where tblA.[TicketDeliverNo] = tblB.[TicketDeliverNo]" & _
    "And tblB.[AmtPaid]>1"
    DoCmd.RunSQL strSQL
    DoCmd.Close acForm, "FRMB"

    Exit Sub
    errorcatch:
    MsgBox Err.Description

    It appears to run OK as it advises you that it is about to run a Delete query.

    Can I ask why you have the same data in 2 different tables?
     

    Attached Files:

  5. BoaterJohn

    BoaterJohn Thread Starter

    Joined:
    Aug 2, 2011
    Messages:
    116
    OBP, I did see where it gives the delete warning, but states that zero records were deleted. The criteria of the sql shows 2 records that fit the the "TicketDeliverNo" and "AMTPAID">1
    Any Input?
     
  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Use debug.print to print your SQL statement to the immediate window and check it to make sure it is accurately being generated.
     
  7. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Well, there is no Join in the SQL, so I would have built the SQL first by making a Delete Query first.
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    A delete query with both tables does not work, whereas the Select query does.
    It gives an Identify Table to delete from error.
    A delete query based on the output of the working Select query also does not work.
    I can probably do it OK in VBA though.
     
  9. BoaterJohn

    BoaterJohn Thread Starter

    Joined:
    Aug 2, 2011
    Messages:
    116
    thank you both, OBP and Rollin Again for your inputs. OBP, your idea about a join clause might do the trick I hope; and debug.print is a good idea to see what's happening in the immediate window. If all else fails, I will attempt a vba Proc to do the deletes if the sql won't work with the two tables.
    Thanks Again. I let you know what I come up with.
     
  10. BoaterJohn

    BoaterJohn Thread Starter

    Joined:
    Aug 2, 2011
    Messages:
    116
    DELETE tNotices.*, tNotices.TicketNo, tHistoryTickets.AmtPaid
    FROM tHistoryTickets INNER JOIN tNotices ON tHistoryTickets.TicketNo = tNotices.TicketNo
    WHERE (((tNotices.TicketNo)=[tHistoryTickets].[TicketNo]) AND ((tHistoryTickets.AmtPaid)>1));
    in a qry did the trick with a access delete qry and both tables used
    Thanks again OBP and Rollin Again
     
  11. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Well done (y)
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1144823

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice