Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Posts
    7

    Question Unanswered: ACCESS SQL DELETE from 3 tables using VIEW

    Hi I have been searching the web for a soloution to my problem but to no avail. I wonder if any one can help or point me to a possible solution.

    I am using .ASP and MS ACCESS

    I have three tables with the following fields and more...

    accounts
    account_ID
    account_status
    account_lastaccess
    ....

    orders
    order_ID
    account_ID
    ....

    basket
    order_ID
    ....
    ....


    I am trying to DELETE certain records in the accounts table along with any orders for that account along with all records in basket which belong to those orders so I am not left with any orphans when an ACCOUNT is deleted.

    The code I am attempting to use to DELETE the respective records from basket and orders is :
    Code:
    '## Create primary VIEW
    strSQL = "CREATE VIEW v1 AS SELECT accounts.account_ID AS key1 FROM accounts "
    If Request.Form("action") = "remove_active" Then
        strSQL = strSQL & "WHERE ( accounts.account_status = 2 ) "
    Else
        strSQL = strSQL & "WHERE ( accounts.account_status = 1 ) "
    End if
        strSQL = strSQL & "AND ( accounts.account_lastaccess < '" & intRemoveDate & "' )"
    Response.write "<p>strSQL = " & strSQL		'## Debugging
    
    '## Open DATABASE and EXECUTE SQL to creat VIEW
    Call OpenDBfor("executing")
    
    
    '## Create secondary VIEW
    strSQL = "CREATE VIEW v2 AS SELECT orders.order_ID AS key2 FROM orders "
    strSQL = strSQL & "WHERE ( orders.order_ID = v1.key1 ) "
    Response.write "<p>strSQL = " & strSQL		'## Debugging
    
    '## Open DATABASE for DELETING and DELETE the record
    Call OpenDBfor("executing")
    
    
    '## DELETE basket contents for these ORDERS
    strSQL = "DELETE FROM basket WHERE EXISTS (SELECT * FROM v2 WHERE basket.order_ID = v2.key2)"
    Response.write "<p>strSQL = " & strSQL		'## Debugging
    
    '## Open DATABASE for DELETING and DELETE the record
    Call OpenDBfor("executing")
    		
    		
    '## DELETE ORDERS for these ACCOUNTS
    strSQL = "DELETE FROM orders WHERE EXISTS (SELECT * FROM v1 WHERE orders.account_ID = v1.key1)"
    Response.write "<p>strSQL = " & strSQL		'## Debugging
    
    '## Open DATABASE for DELETING and DELETE the record
    Call OpenDBfor("executing")
    
    
    
    '## Kill the 2 VIEWS
    strSQL = "DROP VIEW v2"
    Call OpenDBfor("executing")
    
    strSQL = "DROP VIEW v1"
    Call OpenDBfor("executing")
    I have left the code out for deleteing the relevant accounts records, as that is the easy bit!

    ( The OpenDBfor("executing") function just makes the DATABASE connection and executes the strSQL string. It is held in common_func.asp)

    The error I am getting is :
    Code:
    Error Type:
    Microsoft JET Database Engine (0x80004005)
    VIEW cannot contain a parameter.
    ..../include/common_func.asp, line 44
    Has anyone any suggestions on either where I am going wrong or an alternative method.

  2. #2
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    I don't believe you can't delete from a view. If you were using anything other than MS Access, I'd tell you to do this through a trigger or cascading deletes... if nothing else, stored procedures.. but you're kinda stuck with Access.. you'll probably need to execute each delete from the ASP
    That which does not kill me postpones the inevitable.

  3. #3
    Join Date
    Oct 2004
    Posts
    7
    Thanks for your reply... not looking good, then.

    However with regards to deleteing from a view, on another page I have managed to get...

    Code:
    strSQL = "CREATE VIEW v1 AS SELECT orders.order_ID AS my_key FROM orders "
    If Request.Form("action") = "remove_creating" Then
        strSQL = strSQL & "WHERE ( orders.order_status = 1 ) "
    Else
        strSQL = strSQL & "WHERE ( orders.order_status = 0 ) "
    End if
    strSQL = strSQL & "AND ( orders.order_date < '" & intRemoveDate & "' );"
    Response.write "<p>strSQL = " & strSQL		'## Debugging
    
    '## Open DATABASE for DELETING and DELETE the record
    Call OpenDBfor("executing")
    
    
    strSQL = "DELETE FROM basket WHERE EXISTS (SELECT * FROM v1 WHERE basket.order_ID = v1.my_key)"
    Response.write "<p>strSQL = " & strSQL		'## Debugging
    
    '## Open DATABASE for DELETING and DELETE the record
    Call OpenDBfor("executing")
    
    strSQL = "DROP VIEW v1"
    Call OpenDBfor("executing")
    ...to work and it does the job a treat....

  4. #4
    Join Date
    Oct 2004
    Posts
    7
    Is there any way I can do it without using VIEW.

    I have never even seen this method used until this afternoon!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •