ADO.DELETE

[kovan]

apologies for too many posts fro me
but i need to graps this ado...

Public Sub LogOffUser(UsrName As String)
    Dim LogOffUserRecord As ADODB.Recordset
    Set LogOffUserRecord = New ADODB.Recordset
    SQL = "SELECT * FROM LoggedUsers WHERE UserName = " & "'" & UsrName & "'"
    LogOffUserRecord.Open SQL, MeterInfo
    LogOffUserRecord.Delete
    Set LogOffUserRecord = Nothing
    End
End Sub

i get error on the logoffuserrecord.DELETE
saying
"object or provider is not capable of performing the operation"

that code should return one record...
and delete it

according to help i read.. .DELETE is default and will delete the current record..

i tried even typing the constants..
still same error..

thanks

[Stevie]

Why don't you try ...

strSQL = "DELETE FROM LoggedUsers WHERE UserName = '" & UsrName & "'"

ConnectionObject.Execute strSQL

Where ConnectionObject is your current connection.

[kovan]

i could use that..
and did

but i still like to know why my problem didn't work

for future reference

[666539]

I think your problem may be in the way you open your recordset. From your code it didn't look like you defined the type of cursor that should be used. When you don't define the type of cursor that should be used it used a forward-only cursor by default. The forward only cursor is a read-only recordset (can't add, edit or DELETE data). Try opening your recordset by using

LogOffUserRecord.Open SQL, MeterInfo,adopendynamic

Then you should be able to delete that record. I hope this works for you.

Try experimenting with the various type of recordsets. I think, but am not sure, that only a subset of these cursors are available when you are working on an access database. They should all work when working on SQL Server. I think it is the dynamic cursor that isn't supported. I'm pretty sure that Access doesn't support server side cursors also. I'm getting a bit off the subject here, but these are things that may drive you crazy trying to figure out at some point that you wouldn't normally know if you didn't read.

[JHausmann]

Originally posted by 666539
I think your problem may be in the way you open your recordset. From your code it didn't look like you defined the type of cursor that should be used. When you don't define the type of cursor that should be used it used a forward-only cursor by default. The forward only cursor is a read-only recordset (can't add, edit or DELETE data). Try opening your recordset by using

LogOffUserRecord.Open SQL, MeterInfo,adopendynamic

Then you should be able to delete that record. I hope this works for you.

Try experimenting with the various type of recordsets. I think, but am not sure, that only a subset of these cursors are available when you are working on an access database. They should all work when working on SQL Server. I think it is the dynamic cursor that isn't supported. I'm pretty sure that Access doesn't support server side cursors also. I'm getting a bit off the subject here, but these are things that may drive you crazy trying to figure out at some point that you wouldn't normally know if you didn't read.

This a good indication why 'defaults' are sometimes bad...