|
 |
access thread: SQL delete statement
Message #1 by wesley.fitzpatrick@s... on Mon, 29 Apr 2002 09:44:09 +0200
|
|
Hi,
I need to run an SQL statement behind a form that will delete rows on one
table where two fields have the same data. for example I want to delete
existing data before writing the new data to the table but I don't want to
use an update query and I don't have the fields on controls on the form. (it
is an unbound form). Here is the code I am using that doesn't seem to work:
strSQL = "delete * from tbl_Control_Bill"
strSQL = strSQL & "INNER JOIN tbl_Import_Equant on tbl_Control_Bill.[DOC
REF] = tbl_Control_Bill.[DOC REF];"
Set qd = db.CreateQueryDef("", strSQL)
qd.Execute
Set qd = Nothing
Can anyone give me some advice?
Thanks,
Wes.
-----
Wesley Fitzpatrick
Syngenta Agro AG
Chemiestrasse 1
Dielsdorf
CH-8157
Switzerland
Tel: +41 (0)1 8558 411
Fax: +41 (0)1 8558 708
Message #2 by "Leo Scott" <leoscott@c...> on Sun, 28 Apr 2002 21:00:58 -0700
|
|
I think you forgot a <space> after tbl_Control_Bill unless you just typed it
into the message wrong.
strSQL = "delete * from tbl_Control_Bill "
strSQL = strSQL & "INNER JOIN tbl_Import_Equant on tbl_Control_Bill.[DOC
REF] = tbl_Control_Bill.[DOC REF];"
Set qd = db.CreateQueryDef("", strSQL)
qd.Execute
Set qd = Nothing
|-----Original Message-----
|From: wesley.fitzpatrick@s...
|[mailto:wesley.fitzpatrick@s...]
|Sent: Monday, April 29, 2002 12:44 AM
|To: Access
|Subject: [access] SQL delete statement
|
|
|Hi,
|
|I need to run an SQL statement behind a form that will delete rows on one
|table where two fields have the same data. for example I want to delete
|existing data before writing the new data to the table but I don't want to
|use an update query and I don't have the fields on controls on the
|form. (it
|is an unbound form). Here is the code I am using that doesn't seem to work:
|
|strSQL = "delete * from tbl_Control_Bill"
|strSQL = strSQL & "INNER JOIN tbl_Import_Equant on tbl_Control_Bill.[DOC
|REF] = tbl_Control_Bill.[DOC REF];"
|Set qd = db.CreateQueryDef("", strSQL)
|qd.Execute
|Set qd = Nothing
|
|Can anyone give me some advice?
|
|Thanks,
|
|Wes.
|
|-----
|Wesley Fitzpatrick
|Syngenta Agro AG
|Chemiestrasse 1
|Dielsdorf
|CH-8157
|Switzerland
|Tel: +41 (0)1 8558 411
|Fax: +41 (0)1 8558 708
|
|
Message #3 by frazerg@t... on Tue, 30 Apr 2002 08:37:31
|
|
May be you typed the joined clause incorrectly.
But never mind it still won't work
The best way to do this is a subquery
Like so
/*******************************************
strSQL = "delete * from tbl_Control_Bill"
strSQL = strSQL & " Where tbl_Control_Bill.[DOC REF] IN
(Select [DOC REF] From tbl_Import_Equant);"
********************************************/
Regards
Greg Frazer
> I think you forgot a <space> after tbl_Control_Bill unless you just
typed it
into the message wrong.
strSQL = "delete * from tbl_Control_Bill "
strSQL = strSQL & "INNER JOIN tbl_Import_Equant on tbl_Control_Bill.[DOC
REF] = tbl_Control_Bill.[DOC REF];"
Set qd = db.CreateQueryDef("", strSQL)
qd.Execute
Set qd = Nothing
|-----Original Message-----
|From: wesley.fitzpatrick@s...
|[mailto:wesley.fitzpatrick@s...]
|Sent: Monday, April 29, 2002 12:44 AM
|To: Access
|Subject: [access] SQL delete statement
|
|
|Hi,
|
|I need to run an SQL statement behind a form that will delete rows on one
|table where two fields have the same data. for example I want to delete
|existing data before writing the new data to the table but I don't want to
|use an update query and I don't have the fields on controls on the
|form. (it
|is an unbound form). Here is the code I am using that doesn't seem to
work:
|
|strSQL = "delete * from tbl_Control_Bill"
|strSQL = strSQL & "INNER JOIN tbl_Import_Equant on tbl_Control_Bill.[DOC
|REF] = tbl_Control_Bill.[DOC REF];"
|Set qd = db.CreateQueryDef("", strSQL)
|qd.Execute
|Set qd = Nothing
|
|Can anyone give me some advice?
|
|Thanks,
|
|Wes.
|
|-----
|Wesley Fitzpatrick
|Syngenta Agro AG
|Chemiestrasse 1
|Dielsdorf
|CH-8157
|Switzerland
|Tel: +41 (0)1 8558 411
|Fax: +41 (0)1 8558 708
|
|
|
|
|
View Cart
