Delete Query - Delete records from multiple tables 1

[runningphan]

I've been having some problems with a delete query where I need to delete records from multiple tables based on a User defined value. I can setup 2 delete queries and it works great but I'd like for the user to provide one value and it is deleted out of both databases.

I have two tables (Transactions and Jobs). Both tables have the common field Job_Id. I want the user to provide a Job_Id and all records in both Transactions and Jobs are then deleted that contain Job_Id.

Here is my SQL statement that I have that does not work. I am currently getting the error "Could not delete from specified tables".

DELETE JOBS.*, TRANSACTIONS.*, JOBS.Job_Id
FROM JOBS INNER JOIN TRANSACTIONS ON JOBS.Job_Id = TRANSACTIONS.Job_Id
WHERE (((JOBS.Job_Id)=[Enter Job #]));

Any Ideas? Thanks!

 

[smedvid]

One option based on your limited requriements...
Setup a cascading Delete relationship under the design of the tables, assuming that you have a one-to-one or one-to-many relationship. Otherwise, a more complex solution would be to create a form that prompts for the input data, then have a command button that constructs the necessary SQL and executes it... htwh,

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[PHV]

A delete query remove records from a SINGLE table.
You have to execute 2 sql statements, eg (VBA code):
Dim JobId
JobId = InputBox("Enter Job #", "Delete a Job")
If IsNumeric(JobId) Then
DoCmd.RunSQL "DELETE FROM TRANSACTIONS WHERE Job_Id=" & JobId
DoCmd.RunSQL "DELETE FROM JOBS WHERE Job_Id=" & JobId
Else
MsgBox "Delete aborted"
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[runningphan]

I tried using the cascading Delete relationship option but it is not available to me even though I have a one-to-many relationship. I am going through an ODBC driver and the driver must not support that option which could be a bigger problem than I think.

I will try the other option but I'm not quite sure how to set it up, I see the code but how do you call the code?

 

[PHV]

how do you call the code?
One way is the Click event procedure of a button.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[runningphan]

I think I'm pretty close. I have the following code being called from the on_click event but for some reason after entering in the Job # from the inputbox it then prompts for the Job_Id as a parameter.

Private Sub Command0_Click()
Dim JobId
JobId = InputBox("Enter Job #", "Delete a Job")
If JobId > "" Then
DoCmd.RunSQL "DELETE FROM TRANSACTIONS WHERE Job_Id=" & JobId
DoCmd.RunSQL "DELETE FROM JOBS WHERE Job_Id=" & JobId
Else
MsgBox "Delete aborted"
End If
End Sub

 

[PHV]

Double check the spelling of the field in both tables.
BTW, as you removed the IsNumeric check, I guess this field is defined as text:[tt]
DoCmd.RunSQL "DELETE FROM TRANSACTIONS WHERE Job_Id='" & JobId & "'"
DoCmd.RunSQL "DELETE FROM JOBS WHERE Job_Id='" & JobId & "'"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[runningphan]

PHV...

Thank you for your help, it was the fact that the field was text and the above code was not in. Worked like a charm and gave me a whole world to start exploring! Thanks again for opening up my eyes to what can be done with a little bit of correctly formatted code

A star for you!