Rows

[bear]

Hi,

I would want to delete the first 10 rows in access.

how can i do about it?

I did the following code, but i do not know how to continue. Please help


DELETE [R16-Debit].*
FROM [R16-Debit]
WHERE

[CreganTur]

The real question here is what are you actually wanting to delete- are you seriously wanting to delete the records that are in positions 1 - 10 in the recordset, or are you wanting to delete these records because of some value in one of the fields?

If it's a field value then it would be a standard SQL DELETE query, but with some WHERE conditions to ensure you're deleting the correct values.

If you're wanting to delete the first 10 records in the recordset, regardless of values, then this could be accomplished via an ADO or DOA connection- if this is the case, then I'll post an example for you.

Although, I can't see any value added from deleting records based only on their position in the recordset; it could be a really poor choice because you don't have any control over what you're deleting. You could accidentally delete an important record.

[bear]

hi,

what do you mean?
i just want to delete the top 10 rows becuase they are figures that i wont need it.

[CreganTur]

If you only need to delete these 10 records this one time, then it would be best to do it manually- open the table and delete them.

[bear]

No, it would be a rountine..
Ona monthly basis, my team mate will send me a document and i will import to access. the document will always be on the same format.
i am using a macro to run the entire file...that includes deleting the top 10 records. i thought it would be easy for me if i will to make a query and run it as a marco...

[CreganTur]

The only way to delete records based on their location in a recordset that I'm aware of is to use a DAO connection(when working in the current database). The following code creates a DAO connection to your database, and will delete the records that are in places 1 - 10 in the recordset:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim n As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("TableName") '<<<Replace wth your table's name
n = 1
rst.MoveFirst '<<<Move to first record in recordset

Do Until n > 10
rst.Delete '<<<Delete current record
rst.MoveNext '<<<Move to next record
n = n + 1 '<<<Increase counter value
Loop
MsgBox "First ten records have been deleted."

rst.Close
Set rst = Nothing
db.Close
Set db = Nothing

Now... this can cause problems because when you view your table in datasheet view, you might not be viewing it in recordset order... so the first 10 records you see in your table could be records#22-32... or records#11,53,54,18,89,26,12,1,89,52

So test this thoroughly before you put it into production!

HTH:thumb