Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2012
    Posts
    2

    Unanswered: Access & MySQL, 'Database engine stopped the process'

    Hi All,

    I'm in the process of switching out an Access Backend (2007) with MySQL Server 5.5 (locally) with a OBDC 3.51 connector (because the most recent connector just exits the Access front end upon linking).

    Before I linked to MySQL, this function always worked perfectly. The first time after linking, it still works perfectly, but the second time and every time after that (until I delete and reimport the data in the tables), I get this error at the SHRS.MoveFirst underlined below:

    "Run-time error '-2147467259(80004005)'

    The Microsoft Access database engine stopped the process because you and another user are attempting to change the same data at the same time."

    I really have no idea what's wrong...any help is appreciated. I'm only a couple months into VBA programming so general tips are welcome too. Finally, anyone with warnings about problems moving from Access to MySQL, a heads up would be great.

    Function Scrubber()

    DoCmd****nSQL "DELETE * FROM SCRUBEXCEPTIONS;"

    Dim HHCnn As ADODB.Connection
    Set HHCnn = CurrentProject.Connection
    Dim HHRS As New ADODB.Recordset
    HHRS.ActiveConnection = HHCnn

    HHRS.CursorLocation = adUseClient
    HHRS.CursorType = adOpenDynamic
    HHRS.LockType = adLockOptimistic

    Dim SECnn As ADODB.Connection
    Set SECnn = CurrentProject.Connection
    Dim SERS As New ADODB.Recordset
    SERS.ActiveConnection = SECnn

    SERS.CursorLocation = adUseClient
    SERS.CursorType = adOpenDynamic
    SERS.LockType = adLockOptimistic

    Dim SHCnn As ADODB.Connection
    Set SHCnn = CurrentProject.Connection
    Dim SHRS As New ADODB.Recordset
    SHRS.ActiveConnection = SHCnn

    SHRS.CursorLocation = adUseClient
    SHRS.CursorType = adOpenDynamic
    SHRS.LockType = adLockOptimistic

    Dim ScrubCnn As ADODB.Connection
    Set ScrubCnn = CurrentProject.Connection
    Dim ScrubRS As New ADODB.Recordset
    ScrubRS.ActiveConnection = ScrubCnn

    ScrubRS.CursorLocation = adUseClient
    ScrubRS.CursorType = adOpenDynamic
    ScrubRS.LockType = adLockOptimistic

    HHRS.Open "SELECT * FROM HHIDdetails"
    SHRS.Open "SELECT Shareholders.CONTROL_NUMBER, Shareholders.VotePattern, Shareholders.VoteTime, Shareholders.TFT_HHID FROM Shareholders;"

    ScrubRS.Open "Scrub"
    SERS.Open "ScrubExceptions"

    While Not ScrubRS.EOF
    If Not IsNull(ScrubRS(19)) Then
    If ScrubRS(19) = "ADP" Then
    SHRS.MoveFirst
    SHRS.Find ("CONTROL_NUMBER = " & ScrubRS(2))
    If Not SHRS.EOF Then
    SHRS(1) = "ADP"
    SHRS(2) = ScrubRS(1)
    Else
    GoTo missingNumber
    End If
    HHRS.MoveFirst

    HHRS.Find ("ALLHHID = " & SHRS(3))
    If Not HHRS.EOF Then
    HHRS(5) = HHRS(5) - ScrubRS(16)
    If ScrubRS(4) = "B" Then
    HHRS(7) = HHRS(7) - ScrubRS(16)
    Else
    HHRS(6) = HHRS(6) - ScrubRS(16)
    End If
    Else
    MsgBox "Voter not in household! Control: " & ScrubRS(2) & " HHID: " & SHRS(3)
    End If
    Else
    SHRS.MoveFirst
    SHRS.Find ("CONTROL_NUMBER = " & ScrubRS(2))
    If Not SHRS.EOF Then
    If SHRS(1) = "Unvoted" Then
    DoCmd****nSQL "INSERT INTO SCRUBEXCEPTIONS (CONTROL_NUMBER) VALUES (" & ScrubRS(2) & ");"
    GoTo missingNumber
    End If
    SHRS(1) = ScrubRS(18)
    SHRS(2) = ScrubRS(1)
    HHRS.MoveFirst
    HHRS.Find ("ALLHHID = " & SHRS(3))
    If HHRS.EOF Then
    MsgBox "household not found"
    GoTo missingNumber
    End If

    HHRS(5) = HHRS(5) - ScrubRS(16)
    If ScrubRS(4) = "B" Then
    HHRS(7) = HHRS(7) - ScrubRS(16)
    Else
    HHRS(6) = HHRS(6) - ScrubRS(16)
    End If

    End If
    End If
    End If
    missingNumber:
    ScrubRS.MoveNext
    Wend
    HHCnn.Close
    SECnn.Close
    ScrubRS.Close
    ScrubCnn.Close
    SHCnn.Close

    End Function

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As the server tables seems to be attached in the Access database:
    Code:
    DoCmd****nSQL "INSERT INTO SCRUBEXCEPTIONS (CONTROL_NUMBER) VALUES (" & ScrubRS(2) & ");"
    I do not understand why you bother to use ADO to work with these, specially because the many options you use when you create the ADO objects: adUseClient, adOpenDynamic, adLockOptimistic, etc offer no warranty of beeing actually used. see: Visual Basic 6 ADO Tutorial or Access help.

    Can't you simply issue the SQL commands to the Currentdb object (Currentdb.Execute <SQL string>)?
    Have a nice day!

  3. #3
    Join Date
    Aug 2012
    Posts
    2
    Maybe the adUseClient doesn't do anything, but the other two need to be there to allow editing the records.

    I think currentDB.execute can't return values? I'm doing a lot of if/then, sums etc.

Tags for this Thread

Posting Permissions

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