Results 1 to 2 of 2
  1. #1
    Join Date
    May 2006
    Posts
    4

    Unanswered: "Could not start transaction" and repeated update queries

    Hi everyone,

    In my earlier post "How can I carry out batch UPDATE queries" I managed to find a solution, which worked a few times then I got the error message "Could not start transaction; too many transactions already nested". Apprantly I need to clear my workspace every so often, but I think this was included in my code (as I've said earlier my VB isn't extensive and I got the solution from another site).

    This is my code:

    Private Sub Command237_Click()
    On Error GoTo Err_Command237_Click
    Dim wrk As Workspace, db As Database
    Set wrk = DBEngine(0)
    Set db = wrk(0)

    wrk.BeginTrans

    db.Execute "my sql"

    wrk.CommitTrans

    cmdButton_Click_Exit:
    On Error Resume Next
    ' This'll roll back only if code didn't get as far
    ' as the .CommitTrans otherwise an error occurs but
    ' will be ignored due to the preceeding On Error statement
    wrk.Rollback

    On Error Resume Next

    ' Clean up code
    Set db = Nothing
    Set wrk = Nothing
    Exit Sub

    Screen.PreviousControl.SetFocus
    DoCmd.FindNext

    Exit_Command237_Click:
    Exit Sub

    Err_Command237_Click:
    MsgBox Err.Description
    Resume Exit_Command237_Click

    End Sub

    Does anyone know why the workspace isn't clearing and what I can do to clear it please?

    Thanks in advance,

    Jo

  2. #2
    Join Date
    May 2006
    Posts
    4

    Solution

    Hi

    it's always the little things...

    you must CLOSE the workspace...

    so

    wrk.Rollback
    wrk.Close

    Jo

Posting Permissions

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