Results 1 to 8 of 8
  1. #1
    MicheleF
    Guest

    VBA Error 3617 (VBA 6)

    I am trying to delete a record in a database using dataenvironment and a SQL server 7 database. There is only 1 table with 4 fields. Here is the code:

    Private Sub cmdDelete_Click()
    Reply = MsgBox("Record will be deleted permanently. Proceed?", vbYesNo)
    If Reply = vbYes Then
    On Error Resume Next
    If DataEnvironment1.rsPaintSysControl.Supports(adDele te) Then
    MsgBox "This record supports delete method"
    Else
    MsgBox "This record does NOT support delete method"
    End If
    DataEnvironment1.rsPaintSysControl.Delete

    If Err.Number <> 0 Then
    MsgBox "Could not delete record." & vbCrLf & "ERROR #" & _
    Err.Number - vbObjectError & vbCrLf & Err.Description
    DataEnvironment1.rsPaintSysControl.CancelUpdate
    End If
    End If

    End Sub

    I always get a 3617 error. How do I delete the record?

    Thanks,

    Michele Foley

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA Error 3617 (VBA 6)

    Are you trying to do this from VB or from an Access ADP?
    Charlotte

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA Error 3617 (VBA 6)

    Take a look at knowledgebase article <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/q183/3/15.asp>HOWTO: Write and Validate a Custom Business Object with RDS [Q183315]</A>, which deals with the "undocumented" RDS error 3617, and see if that helps you any.
    Charlotte

  4. #4
    MicheleF
    Guest

    Re: VBA Error 3617 (VBA 6)

    Charolette,

    Thanks for the reply. I am doing this from VB, attaching to a SQL database. Looking at the KB article, I changed locktype to optimistic batch. Now I don't get any errors, but I don't get the record deleted either.

    I have tried update, update batch, resync.

    Any other suggestions?

    Michele

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA Error 3617 (VBA 6)

    Try this:

    DataEnvironment1.rsPaintSysControl.Delete adAffectCurrent

    I've found ADO highly reluctant to take certain actions (like update or delete) if you don't explicitly pass it the scope variable. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    You also need to change your error handling to allow for ADO errors, which are entirely separate from the Jet and system errors you trap with the Err object. Add these to the top of your routine:

    Dim errCurr As ADODB.Error <font color=448800>'holds each ADO error</font color=448800>
    Dim errsCnn As ADODB.Errors <font color=448800>'points to the connection's errors collection</font color=448800>

    Set errsCnn = DataEnvironment1.Connection1.Errors

    This assumes that the name of the connection you're using in the data environment is Connection1. If it's something else, you need to change that part of the Set statement. What you're doing here is setting a reference to the errors collection of the connection objects. It's a lot easier to type and is faster than using the full reference every time.

    Now in the error handler, change what you have now to this:

    <pre>If errsCnn.Count > 0 Then
    For Each errCurr In errsCnn
    MsgBox "Could not delete record." & vbCrLf & "ERROR #" & _
    errCurr.Number - vbObjectError & vbCrLf & errCurr.Description
    Next errCurr
    errsCnn.Clear
    ElseIf Err.Number <> 0 Then
    MsgBox "Could not delete record." & vbCrLf & "ERROR #" & _
    Err.Number - vbObjectError & vbCrLf & Err.Description
    End If

    DataEnvironment1.rsPaintSysControl.CancelUpdate</pre>

    Charlotte

  6. #6
    MicheleF
    Guest

    Re: VBA Error 3617 (VBA 6)

    Thanks for the help, but I'm still not there. Here is the current code:

    Private Sub cmdDelete_Click()
    Dim errCurr As ADODB.Error 'holds each ADO error
    Dim errsCnn As ADODB.Errors 'points to the connection's errors collection
    Set errsCnn = DataEnvironment1.Connection1.Errors

    Reply = MsgBox("Record will be deleted permanently. Proceed?", vbYesNo)
    If Reply = vbYes Then
    On Error Resume Next
    DataEnvironment1.rsPaintSysControl.Delete adAffectCurrent
    DataEnvironment1.rsPaintSysControl.UpdateBatch adAffectCurrent

    ct = DataEnvironment1.rsPaintSysControl.CursorType

    Debug.Print "ct = " & ct
    Debug.Print "errsCnn.Count = " & errsCnn.Count
    Debug.Print "err.number = " & Err.Number

    If errsCnn.Count > 0 Then
    For Each errCurr In errsCnn

    Debug.Print "errcurr = " & errCurr.Number & " " & errCurr.Description

    MsgBox "Could not delete record." & vbCrLf & "ERROR #" & _
    errCurr.Number - vbObjectError & vbCrLf & errCurr.Description
    Next errCurr
    errsCnn.Clear
    ElseIf Err.Number <> 0 Then
    MsgBox "Could not delete record." & vbCrLf & "ERROR #" & _
    Err.Number - vbObjectError & vbCrLf & Err.Description
    End If
    DataEnvironment1.rsPaintSysControl.CancelUpdate
    End If
    End Sub

    Without the update my debug prints yield the following:
    ct = 3
    errsCnn.Count = 0
    err.number = 0

    with the updatebatch i get the following:
    ct = 3
    errsCnn.Count = 1
    err.number = -2147217887
    errcurr = -2147217887 Multiple-step operation generated errors. Check each status value.


    I don't know if I'm making any progress


    Michele <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA Error 3617 (VBA 6)

    You don't need an updatebatch if you're deleting a record. Updatebatch is used for batch updating as, for example, when you sync a persisted recordset back to its source table, or otherwise process multiple records in an update. You shouldn't need it at all for a delete.

    What is ct and where did you declare it? It isn't declared in this routine, so it's hard to tell. One thing that might help is to add an On Error Goto Proc_err line in your code and put the error handling after a Proc_err label so an error will jump directly to it. That will help you track down exactly which error you're dealing with. The problem with the ADO errors collection is that it keeps accumulating until you clear it, so you could have errors from more than one process, which is what the message suggests.

    If you leave out the updatebatch, is the record being deleted? If not, and if you're not getting any ADO errors, then what else is going on in the application? And is Connection1 the actual name of the connection object in the data environment?
    Charlotte

  8. #8
    MicheleF
    Guest

    Re: VBA Error 3617 (VBA 6)

    Thanks Charlotte,

    It seems the problem was all on the DB side. I didn't have permissions set correctly, once I changed them everything worked as it should.

    Michele <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •