Delete TOP X number of records

[PaulMac]

Hi,

Hopefully this is a simple one.
IF x =3

then i want to delete the first 3 records in tabley

(Similar to a TOP select statement: Select Top 3 * from tabley)

How can i do this?

thanks

[Trevor Best]

PaulMac wrote:

Hi,

Hopefully this is a simple one.
IF x =3

then i want to delete the first 3 records in tabley

(Similar to a TOP select statement: Select Top 3 * from tabley)

How can i do this?

thanks

Without trying, dunno: delete top 3 * from mytable?

If not then make a query, e.g. qryMyTop3 as select top 3 blah blah then
use another query to delete * from qryMyTop3.

--
But why is the Rum gone?

[PaulMac]

thanks for responding Trevor.

delete top 3 * from mytable...doesn't work (I'm using an .adp if that
makes any difference)...i definitely tried this approach

the problem with the query approach is the number of records i want to
delete is a variable that is populated from within code...

Trevor Best <nospam@localhost> wrote in message news:<40**********************@auth.uk.news.easyne t.net>...

PaulMac wrote:

Hi,

Hopefully this is a simple one.
IF x =3

then i want to delete the first 3 records in tabley

(Similar to a TOP select statement: Select Top 3 * from tabley)

How can i do this?

thanks

Without trying, dunno: delete top 3 * from mytable?

If not then make a query, e.g. qryMyTop3 as select top 3 blah blah then
use another query to delete * from qryMyTop3.

[David W. Fenton]

pa********@hotmail.com (PaulMac) wrote in
news:1e**************************@posting.google.c om:

delete top 3 * from mytable...doesn't work (I'm using an .adp if
that makes any difference)...i definitely tried this approach

the problem with the query approach is the number of records i
want to delete is a variable that is populated from within code...

First off, TOP 3 is going to be randomly selected unless you include
a non-ambiguous ORDER BY clause (i.e., one that can produce only one
definitive sort order for any group of records).

If you have a variable N for TOP N, you have to do one of two
things:

1. construct the SQL in code and execute it in code.

OR

2. in code, save a stored querydef with the appropriate N value and
execute that.

I see absolutely no reason to do #2 and would also do #1.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Trevor Best]

PaulMac wrote:

thanks for responding Trevor.

delete top 3 * from mytable...doesn't work (I'm using an .adp if that
makes any difference)...i definitely tried this approach

the problem with the query approach is the number of records i want to
delete is a variable that is populated from within code...

ADP makes a difference, use a stored procedure.

create procedure DeleteMyTopN
@paramHowMany int
as
set rowcount @paramHowmany
delete from MyTable

You can call this using a adodb.command object and set the parameter.

[Pieter Linden]

pa********@hotmail.com (PaulMac) wrote in message news:<1e**************************@posting.google. com>...

thanks for responding Trevor.

delete top 3 * from mytable...doesn't work (I'm using an .adp if that
makes any difference)...i definitely tried this approach

the problem with the query approach is the number of records i want to
delete is a variable that is populated from within code...

Trevor Best <nospam@localhost> wrote in message news:<40**********************@auth.uk.news.easyne t.net>...

PaulMac wrote:

Hi,

Hopefully this is a simple one.
IF x =3

then i want to delete the first 3 records in tabley

(Similar to a TOP select statement: Select Top 3 * from tabley)

How can i do this?

thanks

Without trying, dunno: delete top 3 * from mytable?

If not then make a query, e.g. qryMyTop3 as select top 3 blah blah then
use another query to delete * from qryMyTop3.

If you want to change the number of records that get deleted each time
(so say X can take any non-negative value), you would need to create a
string to build the SQL statement and then execute it.

Sub DeleteNRecords(byval intNumRecords As Long)

Dim strSQL as string
strSQL = "DELETE TOP " & intNumRecords & " FROM MyTable ORDER BY
MyField;"
CurrentDB.Execute strSQL, dbFailOnError

End Sub