ADO - Delete single row

[Jan Erik]

Hi,

I want to delete a single row in an Access database using the ROWNUMBER. I think the syntax should be something like this:

"DELETE * FROM ACCESS_TABLE WHERE ROWNUMBER = '99'

But I know that the last part is wrong. If anybody has a solution here I will be very greatful!

Regards
Jan erik

 

[ukmikeb]

Hi

Surely the rownumber would be numeric not text.

hth

 

[Ruddles]

What's ROWNUMBER? An AutoNumber field? A user-maintainable field?

Are you trying to delete the 99th row in a table or are you trying to delete the unique record which has 99 in a particular field?

Perhaps you could show us the table?

 

[Jan Erik]

I'm trying to to delete the 99th row in the table.

Jan Erik

 

[Norie]

Jan

Is that the 99th row after sorting?

Is there any other way of identifying the specific row you want to delete?

The reason I ask is because in Access, like most databases, the 'row number' of a record isn't realy relevant.

Unless of course you are referring to a field with the value 99 in it.

 

[Jan Erik]

Hi,

I'll not sort the table or use a spesific field to identify the row. I know the pfysical ROWNUMBER and I want to use it as the identifier in the SQL command

Jan Erik

 

[Ruddles]

As Norie says, there isn't really the concept of a '99th record' in an Access table: Access may present the table to you in a particular sequence whereby the record you're interested in is displayed in the 99th row, but you can't reliably depend on that record actually being the 99th record in the recordset. It might be today or tomorrow, but one day it won't be.

You need to find a unique key for the record - some combination of data which identifies the record uniquely - and base your DELETE action on that.

(Of course if the unique key for that record is 99, then that's what you use.)

 

[Jan Erik]

OK - Thank's to both of you. I will do it in the ordinary way

Jan Erik