deleting records in two Access tables using TADOQuery

Atool Vegad

unread,

May 13, 2003, 10:02:06 AM5/13/03

to

I have two access tables, related one to many.

I would like to delete records based on a date field range where the date
field is in table one and also delete the related records in table two, the
tables are related by a string field called "reference" in table one and
"Call Reference" in table two.

Could someone give me an sql example please.

Guillermo Castaño A

unread,

May 13, 2003, 12:25:11 PM5/13/03

to

If you always want it to happen (every time that a record on table one is
deleted then delete related records of table two) then you can use a ON
DELETE trigger.
See the SQL Server Books On Line for details

--
Guillermo Castaño A.
www.GrupoMillennium.com
"Atool Vegad" <Atool...@irpc.co.uk> wrote in message
news:3ec0fadd$1...@newsgroups.borland.com...

Emmanuel

unread,

May 13, 2003, 6:39:41 PM5/13/03

to

To:Guillermoc
What has Tquery on Access tables got to do with Triggers in Sql Servers?? I dont think you understood the problem or may be I rather confused.

To Atool:
You might have to use BeforeDelete Event of the master query in Delphi.

procedure Tform1.ADOQuery1BeforeDelete(DataSet: TDataSet);
var
sql:string;
n:integer; //number of affected records
begin

//assuming that reference is string. (if it is a number us %d instead of %s)
sql:=format('DELETE * FROM tbl2 WHERE ref=%s ;',[dataset.fieldbyname('ref').asString])

AdoConnection1.execute( sql,n);

end;
I have not tried the code myself but logically it should work.

Good Luck.

Emmanuel

unread,

May 13, 2003, 7:16:53 PM5/13/03

to

I have posted a response already but I assuemed the data in two related queries. Try this one rather:

procedure Tform1ADOTable1BeforeDelete(dataset:TDataset);
var
q:tadoquery;
begin
//I have assumed AdoTable1 is the master table
q:=tadoquery.create(nil);
q.connection:=TADOTable1.connection;
q.sql.add(format('DELETE * FROM tblDetail WHERE ref=%s ;', [dataset.fieldbyname('ref').asString]);

q.execSql;
q.free;
end;

You will like to put the code in try...except block to catch exception.

Best regards
Emmanuel

Brian Bushay TeamB

unread,

May 13, 2003, 9:40:46 PM5/13/03

to

>I have two access tables, related one to many.
>
>I would like to delete records based on a date field range where the date
>field is in table one and also delete the related records in table two, the
>tables are related by a string field called "reference" in table one and
>"Call Reference" in table two.

Access does not support doing this in a single query.

Something like this is what you want to use for deleting records in the related
table. Once you have done that you can run another query to delete from the
primary table.

Delete Table1
from table1D join table2 D2 on D.someField = D2.somefield
where DateField = :DateParam

--
Brian Bushay (TeamB)
Bbu...@NMPLS.com

Viatcheslav V. Vassiliev

unread,

May 14, 2003, 6:18:51 AM5/14/03

to

Do you have foreign key with cascaded deletes?

----------------------------------------------------------------
Regards,
Viatcheslav V. Vassiliev
http://www.oledbdirect.com
The fastest way to access MS SQL Server, MS Jet (MS Access)
and Interbase (through OLEDB) databases.

"Atool Vegad" <Atool...@irpc.co.uk> сообщил/сообщила в новостях
следующее: news:3ec0fadd$1...@newsgroups.borland.com...

Guillermo Castaño A

unread,

May 14, 2003, 12:21:08 PM5/14/03

to

> To:Guillermoc
> What has Tquery on Access tables got to do with Triggers in Sql Servers??
I dont think you understood the problem or may be I rather confused.

Nothing to do ;)

I missread the question