Why can't I delete records from access database? 1

[Paladyr]

In the following code, it is not letting me delete the entire contents of a table. The user id and password I supply in the connection string DO give me permission to do so (since I can just go into the database and blow the rows away). What could be wrong? FYI, the database prompts you for a user name and password whenever you open it.

Sub UpdateTables()
Dim cnnCon As New Connection
Dim strCon As String
Dim rstCustomers As New Recordset
Dim rstHardware As New Recordset
Dim rstHospitalSys As New Recordset
Dim rstSpecialist As New Recordset

On Error GoTo ErrorHandler

strCon = "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=C:\Backups\controltex database 97.mdb;" & _
"Uid=*****;" & _ 'assume valid username
'and password are here
"Pwd=*****;"

cnnCon.Open strCon

Set rstCustomers = New ADODB.Recordset

'Problem here
rstCustomers.Open "customers", strCnn, , , adCmdTable

rstCustomers.Delete adAffectAll

ErrorHandler:
MsgBox Err
Resume Next
'First error is 3001, then 3704, then 0, then 20
End Sub

 

[StaceyG]

Looks like you are not specifying the cursor type or locktype of your recordset. If you check out what microsoft says below, it would seem that your recordset is being opened for read only which is probably why you can't delete

CursorType
Optional. A CursorTypeEnum value that determines the type of cursor that the provider should use when opening the Recordset. The default value is adOpenForwardOnly.
LockType
Optional. A LockTypeEnum value that determines what type of locking (concurrency) the provider should use when opening the Recordset. The default value is adLockReadOnly.

Here is a sample of specifying the cursortype and locktype:

RSData.Open "disputeentry", CN, adOpenDynamic, adLockPessimistic

Hope that helps
Stacey

 

[Paladyr]

Yep, I think that is it. It has been a while since I used ADO . Thanks!