Delete TOP 5 ??

[Roel018]

Hi all..

I'm simply trying to delete the top most records of my database tabel.. I kind reference the ID's because they vary (first can be 3, second 5, etc.. since records get deleted)..

I try to use DELETE TOP 4 FROM Messages ORDER BY id but I get a syntax error.. als DELETE TOP 4 FROM Messages doesn't work...


Here's my complete (TESTING-) page, while
testing I include the "recordTotalFlash"
var in the URL when calling the asp file:

==============================================


<%

Response.CacheControl = "no-cache"
Response.AddHeader "Pragma", "no-cache"
Response.Expires = -1

Dim DBConn, strDB, strInsertSQL, chatname, messagetxt, messagetime

strDB = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("data/messages.mdb")


recordTotalFlash=Request("recordstotal")

if recordTotalFlash>10 then
response.write "&Records_Above_10"
strDELETESQL = "DELETE TOP 4 FROM Messages ORDER BY id"


Set DBConn = Server.CreateObject("ADODB.Connection")
DBConn.Open strDB
DBConn.execute strDELETESQL

DBConn.close
Set DBConn=Nothing
end if
%>


Hope someone can help me....
thnx
Roel

 

[shaddow]

You should try a diferent aproach, this works on SQL server, not shure if it works on ACCESS.

delete from Messages whene ID in (select top 4 ID from Messages order by ID desc)

________
George, M
Searches(faq333-4906),Carts(faq333-4911)