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