Delete from Multiple tables

[cfbeginner]

Hi There,

I would like to turn the below SELECT query in SQL into Access VBA using the DBConnection.Execute(.....)

SELECT tblCust, tblOrder, tblreserve, tblpayment
FROM tblUsers INNER JOIN
tblOrder ON tblcust.ID = tblOrder.custID INNER JOIN
tblreserve ON tblcust.ID = tblreserve.custID INNER JOIN
tblpayment ON tblcust.ID = tblpayment.custID


The custID will be pass in from the Access form.

Thanks

 

[RoyVidar]

Execute one query per each table you want to delete records from.

[tt]dim strSql as string
strSql = "delete from sometable where somefield = " & me!txtSomeControl
cn.execute ,,adexecutenorecords+adcmdtext
...[/tt]

Sample is with numeric criterion, for text, include single quotes.

Roy-Vidar

 

[cfbeginner]

Hi Royvidar,

Actually, I found out that tblOrders used a different name in he primary key to join the tables.

SELECT tblCust, tblOrder, tblreserve, tblpayment
FROM tblUsers INNER JOIN
tblOrder ON tblcust.ID = tblOrder.personID INNER JOIN
tblreserve ON tblcust.ID = tblreserve.personID INNER JOIN
tblpayment ON tblcust.ID = tblpayment.personID


In this case what should mine delete statement be??

Thanks

 

[RoyVidar]

Still the same answer. Execute one statement (delete query) per each table you wish to delete from. There is sample syntax in my first reply.

Roy-Vidar

 

[cfbeginner]

But the tblorders.personID is not used in the form. Only the tblcust.custID. What I need to do is to delete related records in all tables mentioned.

Thanks

 

[cfbeginner]

Doh! Sorry! I got it!

Thanks for all your help.