Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Deleting records from multiple tables with Sql Server and ADO

Status
Not open for further replies.

dlpastel

Programmer
Aug 8, 2002
114
US
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
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top