Deleting records from multiple tables with Sql Server and ADO

[dlpastel]

I am trying to delete records from several tables in a SqlServer database where each table has a common work order number (called jobnum) I want to use the Invoice.jobnum field to delete records with the same jobnum in several different tables.
Here is the code:

'Delete a invoice
StrSql = "DELETE Install.*, Delivery.*, Payments.*, Orders.*, WalkThrough.* " & _
"FROM ((((Install INNER JOIN Delivery ON Install.jobnum = Delivery.jobnum) INNER JOIN Payments ON Delivery.jobnum = Payments.jobnum) INNER JOIN Orders ON Payments.jobnum = Orders.jobnum) INNER JOIN WalkThrough ON Orders.jobnum = WalkThrough.jobnum) INNER JOIN Invoices ON WalkThrough.jobnum = Invoices.jobnum " & _
"WHERE (((Invoices.jobnum)= '" & rsInvoices!jobnum & "'));"
connectstr = "Driver={SQL Server};Server=SERVER;Database=CTS;Trusted_Connection=yes;"
Set dbCTS = New ADODB.Connection
dbCTS.CursorLocation = adUseClient
dbCTS.Open connectstr
dbCTS.Execute StrSql
End If

It does not work. Any idea why?
I get the error "Incorrect syntax near "*"

Thanks,
Dan

 

[fredericofonseca]

You can only delete from one table at the time.

For this purpose you may be better off creating a stored procedure that will do all deletes in sequence.

Another possibility is to use cascade deletes if you have a well designed database, and if the jobnum is primary key in one table and foreign on the others.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[chiph]

Yes, if the tables have a referential integrity dependency between them, you need to delete from them "top down", working from tables with no dependency, down to the ones that the others depend on.

You'll typically want to do this inside a transaction, so that all deletes succeed (CommitTrans), or they all fail (RollbackTrans) in case of a problem. You don't want inter-dependent tables with rows only removed from half of them.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first