ACCESS SQL DELETE from 3 tables using VIEW

dibley · #1 (**permalink**) 12-07-04, 17:58

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.

Seppuku · #2 (**permalink**) 12-07-04, 18:12

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

dibley · #3 (**permalink**) 12-07-04, 18:22

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....

dibley · #4 (**permalink**) 12-07-04, 18:24

Is there any way I can do it without using VIEW.

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

Internet Services

Web Development

Digital Marketing

Consumer Tech